Soup Recipes

Have you ever be given a task to use SQL Server T-SQL to find out the date value of next day, at 00 hour? Say, now is '2007-10-26 13:30:50', and you need to get a '2007-10-27 00:00:00'?

Naturally, I think most of us will do something like this:

declare @today datetime

declare @tomorrow datetime

set @today = convert(datetime, convert(nvarchar(4), datepart(yyyy, getdate())) + convert(nvarchar(2), datepart(mm, getdate())) + convert(nvarchar(2), datepart(dd, getdate())))

set @tomorrow = dateadd(dd, 1, @today)

A lot of datepart and convert involved just to get the day/month/year value. There is a much simpler way actually, considering that datetime internal value is in fact numeric.

declare @today datetime

declare @tomorrow datetime

set @today = convert(bigint, dateadd(hh, -12, getdate()))

set @tomorrow = dateadd(dd, 1, @today)

Note the part that convert datetime to bigint. That's where the rounding took place. Let's step back a little bit and see how that works.

select getdate() as datetimeType, convert(bigint, getdate()) as bigintType, convert(decimal(18,12), getdate()) as decimalType

 

datetimeType            bigintType decimalType

------------            ---------- -----------

2007-10-26 13:50:27.560 39380      39379.576707870372

 

(1 row(s) affected)

From the SQL query above, we can see that a datetime can be converted to decimal type. The numbers on the left hand side of the "." denotes the number of days since "1900-01-01 00:00:00". The ones on the right hand side denotes the fraction of milliseconds in a day. That means, "1.0" means 1 day, means 24 hour, means 1440 minutes, means 86400 seconds, means 86400000 milliseconds. That means, this query below:

select convert(datetime, (convert(decimal(18,12), @tomorrow) + (2.0000000000000/24))) as tom_2am

select dateadd(hh, 2, @tomorrow) as tom_2am

is a lot easier to understand. :D

Oh? Why "dateadd(hh, -12, getdate())", you ask? That's because of the rounding during conversion to bigint. So, let's see a few time during the day:

Any time before 12 noon on the day (< 0.5 in numeric) will be rounded down. Any other time will be rounded up. So, if we want to get today at mid night time value, we will need a source range from yesterday noon to today before noon, that can be rounded to today midnight. Hence, the "-12 hours" above.

PS: I have always wanted to write about this, but since I don't think it'll make any much difference to programming, I've always canceled writing this. Until I asked myself, "how worse can writing this be?", and I don't have the answer. :D If you have a better ways of doing this, please let us know.