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.

This might interest Windows Mobile apps developers out there. Tan Loke Uei (Microsoft's technical product manager of the Mobile Communications Business division) mentioned in his blog that one of his buddy, Richard Jones (of Best Developer Award fame), wrote a Web SERVER in .NET Compact Framework that runs on a Windows Mobile device!

Just imagine the possibilities then.