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.
Upvotes: 53<=-=Jan 3 2008 4:40PM=-=>
Other suggestions, just to be fun:
DATEDIFF2<=-=Jan 4 2008 11:35AM=-=>
Or just call it datediff, but add new dateparts: bigms, bigmis, bigns, bigday, bigdeal.<=-=Jan 5 2008 3:02PM=-=>
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.<=-=Jan 17 2008 1:37AM=-=>
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.
thanks-michael<=-=Jun 30 2008 12:41PM=-=>
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.<=-=May 4 2012 6:19AM=-=>
It’s a shame this didn’t make it into 2012 along with other extensions e.g. DATEFROMPARTS.<=-=Jul 27 2015 7:04AM=-=>
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 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):
WITH AD AS (
SELECT distinguishedName, userAccountControl, NULLIF, 9223372036854775807) AS ax
FROM OpenQuery ( ADSI, ‘<LDAP://your.domain.here>; (&(objectClass=user)(sAMAccountName=test)); distinguishedName, userAccountControl, accountExpires; subtree’)
, CASE userAccountControl & 2 WHEN 0 THEN ‘enabled’ ELSE ‘disabled’ END AS IsActive
, DATEADD) 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.
This has been implemented in SQL Server 2016. See https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-big-transact-sql