Useful tips and tricks for dealing with date/time in SQL

How do a I remove the time from a date/time stamp?

declare @date datetime
set @date = getutcdate()

-- method 1
select cast(floor(cast(@date as float)) as datetime)

-- method 2 --
select dateadd(day, datediff(day, '20000101', @date), '20000101') 

Method 1 works by converting the datetime into a floating value and then rounding the number down to the closest integer. This works because internally, SQL Server represents the day as an integer from epoch and the time as the decimal value. So, when you round the number down all your left with is the date.

Method 2 works by calculating the difference in days from a base date and then add that difference back to the base date. The first method seems to be a little more efficient, but I included Method 2 because I use that technique on many of the other datetime conversions.

How do I get next day's date without time?

declare @date datetime
set @date = getutcdate()

select dateadd(day, datediff(day, '20000101', @date), '20000102') 

This works by finding the difference in days from the base date of Jan 1, 2000 and then adding that difference to the date of Jan 2, 2000—which is one day greater than the base date.

How do I get previous day's date without time?

declare @date datetime
set @date = getutcdate()

select dateadd(day, datediff(day, '20000102', @date), '20000101') 

This works by finding the difference in days from the base date of Jan 2, 2000 and then adding the difference to the date of Jan 1, 2000—which is one day less than the base date.

How do I get a datetime rounded to the nearest day?

declare @date datetime
set @date = getutcdate()

select dateadd(day, datediff(day, '20000101', dateadd(hour, 12, @date)), '20000101') 

This works by adding 12 hours (a half day) to our original datetime. This means if our original time was after 12pm, our date would move to the next day, otherwise the day would remain the same. When then use the same technique we use to drop the time from a datetime stamp.

How do I get a datetime rounded down to the current hour?

declare @date datetime
set @date = getutcdate()

select dateadd(hour, datediff(hour, '20000101', @date), '20000101')

Here we use the same technique we use to drop the time from datetime stamp, but instead of instead we base our calculation on hours—which will give us the datetime stamp rounded to the current hour. This can be extremely useful if you want to find all dates that happened within a specific time of date (like during the hour of 8am.)

How do I get the datetime rounded to the nearest hour?

declare @date datetime
set @date = getutcdate()

select dateadd(hour, datediff(hour, '20000101', dateadd(minute, 30, @date)), '20000101')

Once again, we use the same basic technique we use when rounding to the nearest day, but we base our calculation on minutes and hours. First we add 30 minutes to the datetime stamp and then we calculate the number of hours since Jan 1, 2000 and then add the difference in hours back to our base time of Jan 1, 2000.

How do I find the first business day (Monday) of the current work week?

select dateadd(day, (datediff(day, '20000103', getutcdate()) / 7) * 7, '20000103') 

This tip uses the fact that when you divide a float by an integer, your result is an integer. So, instead of just use a base date of Jan 1, 2000, we instead want to use a base date that is a Monday—which for our example is Jan 3, 2000. The first thing that happens is we divide the datetime by 7. Since we're dividing by an integer, our result will also be integer—which is also the number of times a Monday has occurred since Jan 3, 2000. So, when we take that number and multiple it by 7 again, we're going to get the closest Monday to the currrent datetime stamp.

To find other days of the week, see the base date chart.

How do I find the previous Monday even if current date is Monday?

select dateadd(day, (datediff(day, '20000104', getutcdate()) / 7) * 7, '20000103') 

This tip uses the exact same logic as the previous tip, except we're going to calculate our original base date based on the first Tuesday in 2000, instead of the first Monday. This will ensure that we always see the previous Monday.

As you can see, almost all of these tips are based on calculating time differences between a known base time and a datetime stamp. In the last two tips, it shows ways to calculate the closest day of the week based on a datetime stamp. The chart below shows you other base times you can use to find other days of the week:

How do I find orders placed during the current work week (Monday through Friday?)

select
    *
from
    Orders
where
    OrderDate between dateadd(day, (datediff(day, '20000103', getutcdate()) / 7) * 7, '20000103') and dateadd(day, (datediff(day, '20000107', getutcdate()) / 7) * 7, '20000107')

This works by using the same above techniques to calculate the nearest day of the week to the current datetime stamp. All we're doing here is saying: "Show us all orders between Monday of this week and Friday of this week."