How to select date without time in SQL

Wield the power of SQL's date truncating techniques

Every SQL Management System has its tools for date truncation. Let's dig into these approaches:

SQL Server: Evicting time from the party with CONVERT

In SQL Server, CONVERT is a command that multiplies the values:

SELECT CONVERT(date, YourDateTimeColumn) FROM YourTable;


MySQL & Oracle: DATE_FORMAT's and TO_CHAR's spotlight moments

In MySQL, wield the power of DATE_FORMAT for date extraction:

SELECT DATE_FORMAT(YourDateTimeColumn, '%Y-%m-%d') FROM YourTable;


Similarly, Oracle has TO_CHAR for the same purpose:

SELECT TO_CHAR(YourDateTimeColumn, 'YYYY-MM-DD') FROM YourTable;


PostgreSQL: Choosing a minimalist approach with CAST

PostgreSQL simplifies with a method akin to SQL Server's:

SELECT YourDateTimeColumn::DATE FROM YourTable;


Gaining mastery over SQL Server's DateTime intricacies

SQL Server offers several date manipulation tools:

Precision dating with DATEDIFF and DATEADD

Join the DATEDIFF and DATEADD tandem to zeroify the time for accuracy:

SELECT DATEADD(day, DATEDIFF(day, 0, YourDateTimeColumn), 0) FROM YourTable; -- Goodbye seconds, we won't miss ya


Mastering the moments with GETDATE()

For an explicit snapshot of today's date, omitting the present moment, employ GETDATE():

SELECT CONVERT(VARCHAR(10), GETDATE(), 120) FROM YourTable; -- Who's got time for time anyways?


Speed dating in WHERE clauses

To sprint through date comparisons, directly apply DATEDIFF in WHERE clauses for a performance boost:

WHERE YourDateColumn <= DATEDIFF(day, 0, GETDATE()); -- Speed up your blind dates 


Perfecting SQL performance and precision

Beyond accurate outputs, effective SQL crafts also involve efficiency and precision.

Grab the date with the right grip

Use CAST for nominal data conversion overhead in obtaining the date output:

SELECT CAST(GETDATE() AS DATE);  -- Time to strip the time


Sovereign speeds in WHERE clauses

Optimize filtering by reducing overhead. Use DATEDIFF without the DATEADD function:

WHERE YourDateColumn <= CAST(DATEDIFF(day, 0, GETDATE()) AS date); -- Filter in a flash


Truncating time with float

FLOOR functions can extinguish time via floating-point arithmetic:

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)  -- Time sinks, date floats


This technique is slightly more laborious and less intuitive than CAST. But remember, "All roads lead to Rome".