{"id":2676,"date":"2025-01-20T18:27:01","date_gmt":"2025-01-21T01:27:01","guid":{"rendered":"https:\/\/www.sqltutorial.org\/?page_id=2676"},"modified":"2025-01-20T20:39:52","modified_gmt":"2025-01-21T03:39:52","slug":"how-to-extract-date-from-datetime-in-sql","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-date-functions\/how-to-extract-date-from-datetime-in-sql\/","title":{"rendered":"How to Extract Date from Datetime in SQL"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn to extract a date from a datetime value in SQL using the CAST<\/code>, CONVERT<\/code>, and DATE<\/code> functions.<\/p>\n\n\n\n

Using CAST Function #<\/a><\/h2>\n\n\n\n

The CAST<\/code> function casts a value of one type to another. Therefore, you can use it to cast a datetime value into a date value.<\/p>\n\n\n\n

For example, the following statement uses the CAST<\/code> function to extract a date from a datetime value:<\/p>\n\n\n

SELECT<\/span> CAST('2025-01-21 10:20:30'<\/span> AS<\/span> DATE<\/span>) date_only;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 date_only\n------------<\/span>\n 2025<\/span>-01<\/span>-21<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Using CONVERT Function #<\/a><\/h2>\n\n\n\n
In SQL Server, you can also use the CONVERT<\/code> function to convert a datetime value to a date value:<\/p>\n\n\n
SELECT<\/span> CONVERT(DATE<\/span>, '2025-01-21 10:20:30'<\/span>) date_only;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 date_only\n------------<\/span>\n 2025<\/span>-01<\/span>-21<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Using DATE Function #<\/a><\/h2>\n\n\n\n
SQLite does not directly support the CAST<\/code> or CONVERT<\/code> function. But you can use the DATE<\/code> function to extract a date from a datetime value. For example:<\/p>\n\n\n
SELECT<\/span> DATE<\/span>('2025-01-21 10:20:30'<\/span>) date_only;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 date_only\n------------<\/span>\n 2025<\/span>-01<\/span>-21<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Databases #<\/a><\/h2>\n\n\n\n
The following lists the databases and functions they support for extracting the data from a datetime value:<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n