How to get DATE from DATETIME Column in SQL?

sql

date-extraction

datetime

sql-performance

byAnton Shumikhin·Jan 9, 2025

To extract the date from a DATETIME, use SQL's CAST function:

SELECT CAST(datetime_col AS DATE) FROM table;

This code transforms 2023-01-01 10:00:00 into 2023-01-01, removing the time component. It's essential to structure your queries correctly when dealing with other operations like summing values or filtering by specific criteria.

Tackling diverse scenarios

Working with DATETIME can vary across different SQL platforms. Here are some tailored solutions for popular SQL flavours:

SQL Server

CONVERT is another method employed in SQL Server:

-- When the day gets tough, consult CONVERT
SELECT CONVERT(DATE, datetime_col) FROM table;

MySQL and PostgreSQL

In MySQL and PostgreSQL, CAST functions as outlined in the fast answer.

Oracle

Oracle tends to utilize the TRUNC function for this:

-- Oracle's unique way of truncating the time. Not gym related.
SELECT TRUNC(datetime_col) FROM table;

TimeZone Concerns

Beware of those timezones! They might impact your date values:

-- Timezone? More like mindzone
SELECT CONVERT_TZ(datetime_col,'GMT','America/New_York') AS converted_datetime 
FROM table;

You can then cast the adjusted datetime_col as a date.

Arithmetic on dates

Here’s how to conduct summing operations based on today's date:

-- Keeping up with transaction Kardashians
SELECT SUM(transaction_amount) FROM transactions 
WHERE CAST(transaction_date AS DATE) = CAST(GETDATE() AS DATE)
AND Card_No = '1234567890';

This query conveniently rolls up all transactions made today for a specific card number.

Performance considerations

To boost performance, remember to index your datetime_col, especially if it’s a frequently queried field.

Deep dive: Advanced date extraction techniques

Formatting dates

Different formats? No problem. SQL Server 2012+ introduced the FORMAT function:

-- 'Cause everybody likes a well-dressed date 
SELECT FORMAT(datetime_col, 'yyyy-MM-dd') AS formatted_date FROM table;

Note that its performance might lag behind CAST or CONVERT.

Subqueries and CTEs

Use subqueries or Common Table Expressions (CTEs) for handling more complex scenarios involving filtering and summation:

-- Here we are dropping some sick CTE beats
WITH DateTransactions AS (
    SELECT *,
    CAST(datetime_col AS DATE) AS transaction_date
    FROM transactions
)
SELECT Card_No, SUM(transaction_amount)
FROM DateTransactions
WHERE transaction_date = '2023-01-01'
GROUP BY Card_No;