How to Insert the Date Literals in SQL

In SQL, date literals refer to a string representation of a data value that is directly included in an SQL statement. Depending on your SQL flavor, you may have various support for data literals. For example, the generic SQL or standard SQL supports multiple formats of date literals which allow you to format the date values in a different order.

It is good to use the correct date format to ensure that the database engine interprets the data in the correct format.

This tutorial explores how you can insert the date literals in an SQL database using the generic SQL and other database flavors such as Oracle and MySQL.

SQL DATE Literal Format

The most common and easiest method of inserting the date literal in SQL is to use the DATE literal format. This format follows the YYYY-MM-DD layout.

The following example shows how to insert a date literal string in an SQL query as shown:

SELECT * FROM orders WHERE order_date = '2023-05-08';

In the given query, the SQL engine interprets the date literal and fetches all the orders where the order date equals to May 8, 2023.

SQL DATETIME Literal Format

In some cases, you may want to insert a date that includes the time section. Luckily, SQL also supports the datetime literals which consists of the duration section as shown in the following format:

YYYY-MM-DD HH:MI:SS

Take the query that is shown in the following:

SELECT * FROM orders WHERE order_datetime = '2023-05-08 23:01:00';

In this case, the previous query selects the orders where the order date and time are equal to May 8, 2023 at 23:01:00.

SQL TIMESTAMP Literal Format

SQL also supports the TIMESTAMP date literals. In this format, SQL allows you to include fractional seconds for more precision, as shown in the following format:

YYYY-MM-DD HH:MI:SS:FF

Here, FF represents the fractional sections.

An example is shown in the following:

SELECT * FROM orders WHERE order_timestamp = '2023-05-08 23:55:03.600000';

In the given SQL statement, we select all orders with the order date and time which are equal to May 8, 2023 at 23:55:03 with 600000 fractional seconds.

Oracle SQL Date Format

As mentioned, various SQL flavors may alter the default format and/or include other formats. It is therefore essential to consult the documentation for your SQL engine to learn more.

In Oracle, however, we have the Oracle date format which follows the DD-MON-YYYY format.

An example query is as follows:

SELECT * FROM orders WHERE order_date = '08-MAY-2023';

There you have it!

For MySQL, you can reference the MySQL 8.0 manual on the date literals on the following provided link:

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html

SQL ISO Date Format

SQL also supports the ISO date format which represents the international dates. It follows the YYYY-MM-DDTHH:MI:SS format where the T is used to separate the date and time.

An example query is as follows:

SELECT * FROM orders WHERE order_date = '2023-05-08T23:00:00';

Conclusion

This tutorial covered the different ways to insert the date literals in SQL including the DATE, DATETIME, TIMESTAMP, ISO date, and Oracle date formats. When inserting the date literals in SQL, it is essential to use the correct format for your database engine to ensure a maximum compatibility.