What Is Date Data Type in SQL Give an Example?

In SQL, the Date data type is used to store dates without the time component. It represents a specific day, month, and year in the format ‘YYYY-MM-DD’. The Date data type is commonly used for storing birthdays, anniversaries, or any other date-related information that does not require precise time information.

Example:

Let’s consider a scenario where we have a table called Employees. This table contains various columns to store employee details such as Name, Date of Birth, Joining Date, and so on.

To define the Date data type for the Date of Birth column, we can use the following syntax:

CREATE TABLE Employees (
   Name VARCHAR(50),
   DOB DATE,
   JoiningDate DATE
);

In this example, we have defined two columns with the Date data type: Date of Birth (DOB) and Joining Date. These columns will store dates in the format ‘YYYY-MM-DD’.

Inserting Data:

To insert data into these columns, we can use the following SQL statement:

INSERT INTO Employees (Name, DOB, JoiningDate)
VALUES ('John Doe', '1990-05-15', '2020-01-01');

This query inserts an employee record with Name as ‘John Doe’, Date of Birth as ‘1990-05-15’, and Joining Date as ‘2020-01-01’ into the Employees table.

Selecting Data:

To retrieve data from these columns, you can use a SELECT statement as follows:

SELECT Name, DOB, JoiningDate
FROM Employees;

This query will return all the records from the Employees table, including the Name, Date of Birth, and Joining Date columns.

Formatting Date Output:

In SQL, you can format the output of a Date data type using various functions. One such function is DATE_FORMAT(), which allows you to format a date according to a specific pattern.

For example, if you want to display the date in a ‘DD-MM-YYYY’ format instead of the default ‘YYYY-MM-DD’ format, you can use the following query:

SELECT Name, DATE_FORMAT(DOB, '%d-%m-%Y') AS FormattedDOB
FROM Employees;

This query will return the Name column and a new column called FormattedDOB, where the DOB values are displayed in the ‘DD-MM-YYYY’ format.

Conclusion:

The Date data type in SQL is used to store dates without time information. It’s commonly used for storing birthdays, anniversaries, or any other date-related information. By understanding how to define and manipulate Date data types in SQL queries, you can effectively work with dates in your database applications.