SQL comes packed with lots of tools, data types, and functions for manipulating, retrieving, and using the date and time information including the current date and time.
In this tutorial, we will learn about the date data type and how to insert a date value into an SQL table.
SQL DATE Type
The DATE data type allows you to store the date values. The following demonstrates how to define a column with the DATE data type:
One advantage of the DATE data type is that it is available in all database engines. This means that a date column is supported in MySQL, PostgreSQL, SQL Server, and Oracle. This makes it versatile and quite easy to remember.
Once we created a column with DATE type, we can insert a date value either using a date literal or a date-related function.
For example, to insert a date literal value, we can use the format as follows:
Where:
- YYYY represents the year with four digits (e.g., 2024)
- MM represents the month with two digits (e.g., 01)
- DD represents the day with two digits (e.g., 01)
NOTE: The valid range for the DATE values is 1000-01-01 to 999-12-31.
Create a Table
The following shows a basic create table statement that contains a date column. This helps us to demonstrate how to use the function:
id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
date_var DATE NOT NULL
);
This should create a table called “t” with the “ID” and “date_var” columns.
Insert a Date
As mentioned, we can insert a date value by either using a date literal or using the date-related functions.
The following example shows how to use a date literal and the “current_date” function to insert the current date into the table:
INTO
t(date_var)
VALUES ('2024-12-2'),
(CURRENT_DATE());
The given example should insert the provided string literal and the current date from the function.
The resulting table is as follows:
--+----------+
1|2024-12-02|
2|2024-01-21|
Conclusion
This tutorial covers the date data type in SQL. It is one of the most fundamental type for storing the date values within the database. We also learned how to insert the date values as string literals or using the date-related functions.
