SQL provides multiple datatypes and functions to handle Date and Time values in a database. This is because Date and Time values are represented in various formats. For instance, there are two common ways to represent a date value: DD/MM/YYYY and MM/DD/YYYY. Similarly, there is more than a single way to represent time values.
For a database to recognize such data given in any format, we make use of multiple datatypes and functions.
The only tricky part about storing the Date and Time data in a database is making sure that the values are inserted in the tables with the same format as the datatype. Different database systems use different datatypes and functions to store and handle the Date and Time data.
Date and time datatypes are used in SQL to store date and time values in various formats. The datatypes available in SQL are listed below.
| S.No. | Datatype & Description | Storage |
|---|---|---|
| 1 | datetime – It stores date and time both from January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds; with the format: YYYY-MM-DD HH:MI:SS. | 8 bytes |
| 2 | datetime2 – It stores date and time both from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds. | 6 – 8 bytes |
| 3 | smalldatetime – It stores date and time both from January 1, 1900 to June 6, 2079 with an accuracy of 1 minute. It is stored in the format of YYYY-MM-DD HH:MI:SS. | 4 bytes |
| 4 | date – It stores date only from January 1, 0001 to December 31 9999, in the format: YYYY-MM-DD. | 3 bytes |
| 5 | time – It store time only to an accuracy of 100 nanoseconds. | 3 – 5 bytes |
| 6 | datetimeoffset – It is the same of the datetime2 with the addition of the time zone offset. | 8 – 10 bytes |
| 7 | timestamp – It stores the unique number that gets updated every time a row gets created or modified. It does not correspond to real time and is based on internal time. Each table may have only one timestamp variable. |
In the following example, let us create a table named SALES_DETAILS which accepts only date and time values in different formats.
To insert values into this table, use the following query −
The table will be created as follows −
| orderDate | shippingDate | deliveredDate | time |
|---|---|---|---|
| 2023-02-01 | 2023-02-01 :10:00 | 2023-02-03 :18:00 | 18:00 |
SQL also provides multiple functions to handle date and time values.
For instance, there are different functions to retrieve the current timestamp in different formats. Let us see some of such functions below −
To get the current date, we use the CURDATE() function in MySQL. The format of the resultant date will be ‘YYYY-MM-DD’ (string) or YYYYMMMDD (numeric).
When we execute the above query, we get the current days date −
The MySQL NOW() function will retrieve the current date and time value as a timestamp based on the context and, the value returned will be in either of the two formats: ‘YYYY-MM-DD hh:mm:ss’ and ‘YYYYMMDDhhmmss’.
When we execute the above SQL query, we get the current date with time as follow −
The MySQL CURRENT_TIMESTAMP() function is used to get the current timestamp. The value returned will be in ‘YYYY-MM-DD hh:mm:ss’ (string) or YYYYMMDDhhmmss (numeric) format. This function is a synonym for NOW().
When we run the above SQL query, we get the following output −