Add datediff_big - by Erland Sommarskog

Status : 


Sign in
to vote
ID 320998 Comments
Status Active Workarounds
Type Suggestion Repros 6
Opened 1/3/2008 3:08:35 PM
Access Restriction Public


In system-monitoring queries it's not uncommon to have things like

   datediff(ms, login_time, getdate())

unfortunately, this dies with overflow if someone has been logged in for more
than 24 days - which system processes often have.

The problem gets even more pressing with the addition of microseconds and
nanoseconds in SQL 2008.
Sign in to post a comment.
Posted by Tom V on 7/4/2017 at 6:12 AM
This has been implemented in SQL Server 2016. See
Posted by Robert Heinig III on 7/27/2015 at 7:04 AM
Just since I didn't get a search hit on "dateadd bigint":

Just take a look at AD timestamps (or venerable NTFS filetimes). Given the pervasiveness of such data, SQL should be able to handle these natively without kludges (of which there are a few - CLR being the cleanest and most uncouth at the same time).

Therefore: Yes please - allow a DATEDIFF_BIG, a DATEADD(x,bigint,y) *AND* the plus operator for date and time. While you're at it, document cleanly in which cases leap seconds are handled properly and when not. (suppressing terms 'literal', 'interval', 'postgresql'... shut up bob!)

An Illustration - given 'the usual' ADSDSOObject-provider linked server (not recommended in prod environments):
    SELECT distinguishedName, userAccountControl, NULLIF(CAST(accountExpires AS bigint), 9223372036854775807) AS ax
    FROM OpenQuery ( ADSI, '<LDAP://>; (&(objectClass=user)(sAMAccountName=*test*)); distinguishedName, userAccountControl, accountExpires; subtree')
SELECT distinguishedName
    , CASE userAccountControl & 2 WHEN 0 THEN 'enabled' ELSE 'disabled' END AS IsActive
    , DATEADD(day, ax/864000000000, DATETIME2FROMPARTS(1601, 1, 1, ax /36000000000 % 24, ax /600000000 % 60, ax /10000000 % 60, ax % 10000000, 7)) AS accountExpiresUTC
... the very cleanest "get-test-accounts-with-expiration-date-from-AD-in-TSQL" i could come up with. Bigint interval handling mightimprove this markedly in readability and CPU cost, though the latter is mitigated by the AD provider already forging Int64 values into strings.
Posted by Paul White NZ on 5/4/2012 at 6:19 AM
It's a shame this didn't make it into 2012 along with other extensions e.g. DATEFROMPARTS.
Posted by MatthewRoche on 6/30/2008 at 12:41 PM
DATEDIFF_BIG would be a great addition to the set of T-SQL functions, and will likely become a true necessity given the new DATETIME2 data type in SQL 2008.
Posted by Microsoft on 1/17/2008 at 1:37 AM
Thanks for the great feedback and suggestion.

It indeed did catch our attention during the early design stage for the new date/time feature in Katmai. Given the time and resource concern, we somehow didn't consider it a higher priority item in comparing others especially due to the lack of convincable user scenarios. The 'system-monitoring queries' scenario seems a very valid one for us to look it more deeply. If not for Katmai, definitely next release if it's highly required.

Given the backward compact concern, we believe that introducing a new built-in (i.e. datediff_big()) would be a better idea especially for apps that might have persisted computed columns and/or indices created with datediff().

Meanwhile, i guess the only considerable workaround is to 'down-grade' the datepart parameter when calling datediff for larger ranges and programm your own logic to calculate the desired part (i.e. microseconds) with bigint variable.


Posted by Hugo Kornelis on 1/5/2008 at 3:02 PM
Don't make the dateparts affect the returned datatype. DATEDIFF should always return int, DATEDIFF_BIG always bigint.

The alternate names suggested by Aaron are inferior to Erlands suggestion. DATEDIFF and DATEDIFF_BIG aligns nicely with the existing COUNT and COUNT_BIG.
Posted by Erland Sommarskog on 1/4/2008 at 11:35 AM
Or just call it datediff, but add new dateparts: bigms, bigmis, bigns, bigday, bigdeal.
Posted by AaronBertrand on 1/3/2008 at 4:40 PM
Other suggestions, just to be fun: