SQL Server – Age Calculations – Day vs Hour

If you need to calculate age in days using SQL Server Transact-SQL, you have lots of options. When your age should be displayed in elapsed “days” for example, you might think that using the DAY attribute in DATEDIFF() would be the way to go. But you may want to use HOUR rather than DAY if your calculations must be accurate.

Why?  Because with DATEDIFF() using DAY, calculations are based on midnight boundaries. This means that your number of days will change if you are before or after the midnight boundary. For example, your actual time-span may have only been a couple of hours  (11:00 pm initially and it is now 1:00 am…only 2 hours later) but a calculation using DAY may show the age as 1 day. Since a day is typically 24 hours, this may not be what you were expecting. So rather than using this:

[sql]

SET @p_days_till_expire = ( DATEDIFF(DAY, CURRENT_TIMESTAMP, @p_expire_date))

[/sql]

You may want to try this instead:

[sql]

SET @p_days_till_expire = ( DATEDIFF(HOUR, CURRENT_TIMESTAMP, @p_expire_date)
/ 24 )

[/sql]

The second option uses HOUR in DATEDIFF which can give you finer control over your actual age calculations, even when displaying as days.  Note that we did have to divide by 24 since in this example we wanted to show the age in days.