SQL - Date & Time

---

---

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 & Time Datatypes in SQL

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.

Example

In the following example, let us create a table named SALES_DETAILS which accepts only date and time values in different formats.

CREATE TABLE SALES_DETAILS(
   orderDate DATE, 
   shippingDate DATETIME, 
   deliveredDate TIMESTAMP, 
   time TIME
);

To insert values into this table, use the following query −

INSERT INTO SALES_DETAILS VALUES
('2023-02-01', '2023-02-01 :10:00','2023-02-03 :18:00', '18:00');

Output

The table will be created as follows −

Date & Time Functions in SQL

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 −

CURDATE() Function

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).

SELECT CURDATE();

Output

When we execute the above query, we get the current days date −

NOW() Function

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'.

SELECT NOW();

Output

When we execute the above SQL query, we get the current date with time as follow −

CURRENT_TIMESTAMP() Function

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().

SELECT CURRENT_TIMESTAMP();

Output

When we run the above SQL query, we get the following output −