How to Extract Date from Datetime in SQL

Summary: in this tutorial, you’ll learn to extract a date from a datetime value in SQL using the CAST, CONVERT, and DATE functions.

Using CAST Function #

The CAST function casts a value of one type to another. Therefore, you can use it to cast a datetime value into a date value.

For example, the following statement uses the CAST function to extract a date from a datetime value:

SELECT CAST('2025-01-21 10:20:30' AS DATE) date_only;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 date_only
------------
 2025-01-21Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using CONVERT Function #

In SQL Server, you can also use the CONVERT function to convert a datetime value to a date value:

SELECT CONVERT(DATE, '2025-01-21 10:20:30') date_only;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 date_only
------------
 2025-01-21Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using DATE Function #

SQLite does not directly support the CAST or CONVERT function. But you can use the DATE function to extract a date from a datetime value. For example:

SELECT DATE('2025-01-21 10:20:30') date_only;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 date_only
------------
 2025-01-21Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Databases #

The following lists the databases and functions they support for extracting the data from a datetime value:

• MySQL CAST function
• PostgreSQL CAST function
• SQL Server CAST and CONVERT functions
• SQLite DATE function