DATEDIFF (SQL)
Synopsis
DATEDIFF(datePart,startDate,endDate)
Description
-
DATEDIFF(datePart,startDate,endDate) returns an INTEGER value that is the difference between the starting and ending date (startDate minus endDate) for the specified date part (seconds, days, weeks, and so on). If endDate is earlier than startDate, DATEDIFF returns a negative INTEGER value.
This statement returns 353 because there are 353 days (D) between the two timestamps:
SELECT DATEDIFF(D,'2022-01-01 00:00:00','2022-12-20 12:00:00')
Example: Calculate Differences Between Dates
DATEDIFF is compatible with Sybase and Microsoft SQL Server. You can perform similar time and date comparisons using the TIMESTAMPDIFF ODBC scalar function.
Arguments
datePart
The name or abbreviated name of a date or time part. You can specify datePart in uppercase or lowercase. In embedded SQL, you specify datePart as a literal value or host variable. This table shows the valid date and time parts:
Name | Abbreviations |
---|---|
year | yyyy, yy |
quarter | qq, q |
month | mm, m |
week | wk, ww |
weekday | dw, w |
day | dd, d |
dayofyear | dy, y |
hour | hh, h |
minute | mi, n |
second | ss, s |
millisecond | ms |
microsecond | mcs |
nanosecond | ns |
The weekday and dayofyear date part values are functionally identical to the day date part value.
DATEDIFF does not handle quarters (3-month intervals).
If you specify start and end dates that include fractional seconds, DATEDIFF returns the difference as an integer number of fractional seconds. For example:
SELECT DATEDIFF('ms','64701,56670.10','64701,56670.27'), /* returns 170 */
DATEDIFF('ms','64701,56670.1111','64701,56670.27222') /* returns 161 */
DATEDIFF returns fractional seconds as milliseconds (a three-digit integer), microseconds (6-digit integer), or nanoseconds (9-digit integer) regardless of the number of fractional digits precision in startDate and endDate. For example:
DATEDIFF performs slightly different operations depending on whether you specify datePart with or without quotes:
-
Quotes: DATEDIFF('month','2018-02-25',$HOROLOG): datePart is treated as a literal. When processing the query, InterSystems SQL performs literal substitution, replacing the 'month' string with an input parameter, which produces a more generally reusable cached query.
-
No quotes: DATEDIFF(month,'2018-02-25',$HOROLOG): datePart is treated as a keyword. When processing the query, InterSystems SQL does not perform literal substitution, which produces a more specific cached query.
In Embedded SQL, specifying an invalid datePart as an input variable returns an SQLCODE -8 error. Specifying an invalid datePart as a literal value returns a <SYNTAX> error.
In Dynamic SQL, if you supply an invalid datepart, DATEDIFF returns NULL. No SQLCODE error is issued.
startDate,endDate
The starting and ending dates over which DATEDIFF calculates the difference, specified as one of these InterSystems IRIS® data types:
-
%Date logical value (+$H), also known as $HOROLOG format.
-
%PosixTime (%Library.PosixTimeOpens in a new tab) logical value (an encoded 64-bit signed integer).
-
%TimeStamp (%Library.TimeStampOpens in a new tab) logical value (YYYY-MM-DD HH:MM:SS.FFF), also known as ODBC format.
-
%String or string-compatible value, which can be in one of these formats:
$HOROLOG Date and Time FormatFormat Example ddddd SELECT DATEDIFF('yy','65726','66716') ddddd,sssss SELECT DATEDIFF('mi','65726,143','66716,256') ddddd,sssss.fff SELECT DATEDIFF('ns','65726,143.345','66716,256.467') where:
-
ddddd is the integer number of days since December 31, 1840.
-
sssss is the integer number of seconds since the start of that day.
-
fff is the integer number of fractional seconds.
Date FormatFormat Example MM/DD/YY SELECT DATEDIFF('yy','11/25/80','12/31/99') MM/DD/YYYY SELECT DATEDIFF('dd','3/15/2017','8/24/2022') MM-DD-YY SELECT DATEDIFF('yy','11-25-80','12-31-99') MM-DD-YYYY SELECT DATEDIFF('dd','3-15-2017','8-24-2022') MM.DD.YY SELECT DATEDIFF('yy','11.25.80','12.31.99') MM.DD.YYYY SELECT DATEDIFF('dd','3.15.2017','8.24.2022') Mmm DD YY SELECT DATEDIFF('ss','Sep 9 91','Dec 30 92') Mmm DD YYYY SELECT DATEDIFF('ss','October 10 2019','January 23 2021') Mmm DD, YY SELECT DATEDIFF('ss','Sep 9, 91','Dec 30, 92') Mmm DD, YYYY SELECT DATEDIFF('ss','October 10, 2019','January 23, 2021') where:
-
MM is the two-digit month.
-
DD is the two-digit number of days in the month.
-
Mmm is the spelled-out month. You can specify a minimum of three letters (for example, Mar) up to the full month name (for example, March).
-
YY and YYYY are the two-digit and four-digit forms of the year, respectively.
You can specify startDate and endDate as combined date and time strings. For example:
SELECT DATEDIFF('hh','12/22/2021 8:15:23','12/31/2021 10:30:23')
If you specify a time without a date, DATEDIFF defaults to date 01/01/1900.
Time FormatFormat Example HH: SELECT DATEDIFF('hh','2:','10:') HH:MM SELECT DATEDIFF('mi','2:15','10:30') HH:MM:SS SELECT DATEDIFF('ss','2:15:23','10:30:59') HH...SS:FFF SELECT DATEDIFF('ms','2:15:23:335','10:30:59:245') HH...SS.FFF SELECT DATEDIFF('ms','2:15:23.335','10:30:59.245') HH...[AM|PM] SELECT DATEDIFF('mi','2:15AM','10:30PM') where:
-
HH is the two-digit number of hours into the day.
-
MM is the two-digit number of minutes into the hour.
-
SS is the two-digit number of seconds into the minute.
-
FFF is the number of fractional seconds.
You can specify startDate and endDate as combined date and time strings. For example:
SELECT DATEDIFF('hh','12/22/2021 8:15:23','12/31/2021 10:30:23')
If you specify a date without a time, DATEDIFF defaults to time 00:00:00.
-
You can specify startDate and endDate arguments in different data types.
startDate and endDate arguments have these restrictions and behaviors:
-
A date string must be complete and properly formatted with the appropriate number of elements and digits for each element and the appropriate separator character. Years must be specified as four digits. If you omit the date portion of an input value, DATEDIFF defaults to '1900-01-01'.
-
Date and time values must be within the valid range:
-
Years — 0001 through 9999
-
Months — 1 through 12
-
Days — 1 through 31
-
Hours — 00 through 23
-
Minutes — 0 through 59
-
Seconds — 0 through 59
The number of days in a month must match the month and year. For example, the date '02-29' is valid only if the specified year is a leap year. An invalid date value results in an SQLCODE -8 error.
-
-
In date values less than 10 (month and day) a leading zero in optional. Other non-canonical integer values are not permitted. For example, a Day value of '07' or '7' is valid, but '007', '7.0', or '7a' are not valid.
-
Time values are optional. If startDate or endDate specifies an incomplete time, zeros are supplied for the unspecified parts.
-
An hour value less than 10 must include a leading zero. Omitting this leading zero results in an SQLCODE -8 error.
-
In Embedded SQL, specifying an invalid startDate or endDate as either an input variable or literal returns an SQLCODE -8 error.
-
In Dynamic SQL, if you supply an invalid startDate or endDate, DATEDIFF returns NULL. No SQLCODE error is issued.
-
Two-digit years from 01 to 99 are assumed to be from 1901 to 1999. For example, in this statement, the startDate year is 1914:
SELECT DATEDIFF('year','10/11/14','04/22/2022'), DATEDIFF('year','12:00:00','2022-04-22 12:00:00')
Specifying 00 is treated as year 0000, which is invalid and returns an error.
To change the default sliding window that controls this date system-wide, use the ^%DATE legacy utility. For information on establishing a sliding window for interpreting a specified date with a two-digit year, see the $ZDATE, $ZDATEH, $ZDATETIME and $ZDATETIMEH functions.
Examples
Calculate Differences Between Dates
DATEDIFF returns the total number of the specified unit between startDate and endDate. For example, this statement calculates the number of minutes between dates. It evaluates both the date and time components. For each day difference, it adds 1440 minutes, which is the number of minutes in a day.
SELECT DATEDIFF('mi','1910-08-21 08:32:04','1910-08-28 01:45:00')
DATEDIFF does not account for the actual duration between dates. In this way, it can be considered as a count of the specified date part boundaries crossed between startDate and endDate. For example, these differences between consecutive years all return a DATEDIFF of 1, even though their durations are greater than or less than 365 days.
SELECT DATEDIFF('yyyy','1910-08-21','1911-08-21') AS ExactYear,
DATEDIFF('yyyy','1910-06-30','1911-01-01') AS HalfYear,
DATEDIFF('yyyy','1910-01-01','1911-12-31') AS Nearly2Years,
DATEDIFF('yyyy','1910-12-31 11:59:59','1911-01-01 00:00:00') AS NewYearSecond
Similarly, in this statement, the difference these consecutive minutes is 1, even though only 6 seconds actually separate the two values.
SELECT DATEDIFF('mi','12:23:59','12:24:05') AS MinuteDiff
The previous statements used abbreviations for the date part. Alternatively, you can specify the full name of the date part. For example:
SELECT DATEDIFF('minute','12:23:59','12:24:05') AS MinuteDiff
This Embedded SQL example uses host variables to perform the same DATEDIFF operation as the previous statement:
set datePart="minute"
set startDate="12:23:59"
set endDate="12:24:05"
&sql(SELECT DATEDIFF(:datePart,:startDate,:endDate) INTO :diff)
WRITE diff, !
This statement uses DATEDIFF in the WHERE clause to select patients admitted in the last week:
SELECT Name,DateOfAdmission FROM Sample.Patients WHERE DATEDIFF(D,DateOfAdmission,$HOROLOG) <= 7
This statement uses a subquery to return those records where the person’s date of birth is 1500 days or fewer from the current date:
SELECT Name,Age,DOB
FROM (SELECT Name,Age,DOB, DATEDIFF('dy',DOB,$HOROLOG) AS DaysTo FROM Sample.Person)
WHERE DaysTo <= 1500
ORDER BY Age
Time Differences Independent of Time Format
DATEDIFF returns a time difference in seconds and milliseconds, even when the time format for the current process is set to not return seconds. For example:
// Get current time format and set start and end times
set originalTimeFormat = ##class(%SYS.NLS.Format).GetFormatItem("TimeFormat")
set startDate = "66211,34717.10"
set endDate = "66211,34720.27"
// Set time format that includes seconds (TimeFormat = 1)
do ##class(%SYS.NLS.Format).SetFormatItem("TimeFormat",1)
write "DATETIME (with seconds): ",!, $ZDATETIME(startDate,1,-1)," ",$ZDATETIME(endDate,1,-1),!
&sql(SELECT DATEDIFF('ss',:startDate,:endDate) INTO :diff)
write "DATEDIFF number of seconds: ",diff,!!
// Set time format that omits seconds (TimeFormat = 2)
do ##class(%SYS.NLS.Format).SetFormatItem("TimeFormat",2)
write "DATETIME (without seconds): ",!, $ZDATETIME(startDate,1,-1)," ",$ZDATETIME(endDate,1,-1),!
&sql(SELECT DATEDIFF('ss',:startDate,:endDate) INTO :diff)
write "DATEDIFF number of seconds: ",diff,!
// Revert to original time format
do ##class(%SYS.NLS.Format).SetFormatItem("TimeFormat",originalTimeFormat)
Alternatives
To call this function in ObjectScript code, use the DATEDIFF()Opens in a new tab method:
$SYSTEM.SQL.DATEDIFF(datePart,startDate,endDate)
Specifying an invalid datepart to the DATEDIFF() method generates a <ZDDIF> error.
See Also
-
^%DATE legacy documentation at https://docs.intersystems.com/priordocexcerptsOpens in a new tab