Rounding SQL DateTime to midnight

Handling older versions: SQL Server 2005

If your SQL Server happens to belong to the Jurassic era (version 2005 or prior), sigh inwardly and then use the DATEADD and DATEDIFF functions:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, your_datetime_column), 0) AS RoundedDateTime;


Take the DATEDIFF from the origin (0 is '1900-01-01'), and then DATEADD it back. The time division is lost, whoosh!

WHERE clauses: Bringing precision

Filtering records by date becomes as precise as a Swiss watch when you implement date rounding in WHERE clauses:

-- For 21st-century SQL Server (2008+)
WHERE your_datetime_column >= CAST(GETDATE() - INTERVAL 'X DAY' AS date)

-- For its older cousin (SQL Server 2005)
WHERE your_datetime_column >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() - INTERVAL 'X DAY'), 0)


Swap 'X' with the number of days since today you're interested in. '-6' for a week back, '-365' for a year back, '-7305' for a lifetime — just kidding!

Special scenarios: Tomorrow, yesterday and rounding gone wrong

Midnight a day apart

Sometimes, you might need yesterday's or tomorrow's midnight. Fret not! SQL brings yesterday's errors and tomorrow's hopes within reach:

-- Yesterday's midnight
SELECT DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0) AS YesterdayMidnight;

-- Tomorrow's midnight
SELECT DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), 0) AS TomorrowMidnight;


Here's a magic trick: SQL flipped the day offset. Cool, huh?

Rounding pitfalls

Avoid the common blunder of adding .5 to DateTime, hoping it would round to the nearest day. It's as unreliable as a chocolate teapot!

-- Incorrect approach
SELECT GETDATE() + 0.5; -- It's a trap! Adds roughly 12 hours, not a day!

-- Correct way
SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME);


Refuse the temptation to get creative with floats; stick to the good ol’ date functions.

Server version compatibility

Ensure your script runs smoothly on different SQL Server versions. Choose methods considering your servers' compatibility levels and avoid serving runtime error soup to your users.