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
is equivalent to "
2007-10-27 02:00:00.000". Cool huh? But of course, the query below: 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:
| select convert(decimal(18,12),@dt) | decimal(18,12) | bigint |
| 2007-10-26 00:00:00 | 39379.000000000000 | 39379 |
| 2007-10-26 03:00:00 | 39379.125000000000 | 39379 |
| 2007-10-26 12:00:00 | 39379.500000000000 | 39380 |
| 2007-10-26 15:00:00 | 39379.625000000000 | 39380 |
| 2007-10-27 00:00:00 | 39380.000000000000 | 39380 |
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.