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

Categories: SQL

I've come across several clever and convenient tricks for dealing with dates recently in SQL Server. I previously blogged about how to remove time from date/time stamp in SQL Server, but I've come across a group of other really useful date/time manipulations that can be very useful when dealing with SQL Server.

The basic idea behind most of these tricks is that by using a known base datetime stamp, you can then use simple addition and subtraction to calculate the new dates. For example, if I calculate the difference in days between Jan 9, 2008 at 08:00 Jan 1, 2000 at 00:00, I'll end up with a whole number of days which I can then and add back to Jan 1, 2000 at 00:00 and I'll end up with Jan 9, 2008 at 00:00.

Take the tricks below and play around to see what other useful combinations you can develop. If you have any other useful date/time tricks, let me know!

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:

Day of the Week Base Date
Sunday Jan 2, 2000
Monday Jan 3, 2000
Tuesday Jan 4, 2000
Wednesday Jan 5, 2000
Thursday Jan 6, 2000
Friday Jan 7, 2000
Saturday Jan 8, 2000

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."

Related Blog Entries

Comments

Jake Munson's Gravatar You should change your post title to "...dealing with date/time in SQL Server". Microsoft doesn't own SQL. ;)
Dan G. Switzer, II's Gravatar The basic concepts should work in any database those...
Jake Munson's Gravatar Sure, the basic concepts might work, but the syntax is going to vary widely. Take a look at the docs page for cfqueryparam (http://cfquickdocs.com/cf8/#cfqueryparam) to see a good table that shows how widely the different DBs handle data types.

Sorry, it's just a pet peeve of mine when SQL Server people call Microsoft's database "SQL". I suppose you weren't really doing that, but your title uses the word SQL, but your post is about SQL Server. But some times I need to relax. :)
Lee C.S. Young's Gravatar Thanks for the tips Dan. Your post helped me out big time!

Add Comment

Leave this field empty


If you subscribe, any new posts to this thread will be sent to your email address.