Return date and time difference values
Overview
The return date and time difference value functions covered in this section are:
DATEDIFF
DATDIFF returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.
Syntax
DATEDIFF ( datepart , startdate , enddate )
Return Types
int
Arguments
| Argument | Description | Example |
|---|---|---|
startdate | An expression that can resolve to one of the following values: date, datetime, datetimeoffset, smalldatetime, time. Use four-digit years to avoid ambiguity. | 2007-05-05 12:10:09.3312722 |
enddate | An expression that can resolve to one of the following values: date, datetime, datetimeoffset, smalldatetime, time. Use four-digit years to avoid ambiguity. | 2007-05-04 12:10:09.3312722 |
datepart | The units in which DATEDIFF reports the difference between the startdate and enddate. Commonly used datepart units include month or second. | day |
The units in which DATEDIFF reports the difference between the startdate and enddate. Commonly used datepart units include month or second.
The datepart value can't be specified in a variable, nor as a quoted string like 'month'.
The following table lists all the valid datepart values. DATEDIFF accepts either the full name of the datepart, or any listed abbreviation of the full name.
| datepart |
|---|
| year |
| quarter |
| month |
| dayofyear |
| day |
| week |
| hour |
| minute |
| second |
| millisecond |
| microsecond |
| nanosecond |
Remarks
Use DATEDIFF in the SELECT <list>, WHERE, HAVING, GROUP BY and ORDER BY clauses.
DATEDIFF implicitly casts string literals as a datetime2 type. This means that DATEDIFF doesn't support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.
Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.
DATEDIFF may overflow with a precision of minute or higher if the difference between enddate and startdate returns a value that's out of range for int.
Example 1
Finding the number of days between two dates.
SELECT
DATEDIFF(day, [Created], [Modified]) AS 'Duration'
FROM
[Cinchy].[Tables]
WHERE
[Deleted] IS NULL
Example 2
Specifying user-defined variables for startdate and enddate
DECLARE @startdate datetime2 = '2007-05-05 12:10:09.3312722'
DECLARE @enddate datetime2 = '2007-05-04 12:10:09.3312722'
SELECT DATEDIFF(day, @startdate, @enddate)
Example 3: Specifying scalar system functions for startdate and enddate
SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME())
DATEDIFF_BIG
DATEIFF_BIG returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform. Please check back at a later time. For a full list of in-progress function translations, see the CQL functions reference page.
Syntax
DATEDIFF_BIG ( datepart , startdate , enddate )
Return Types
bigint
Arguments
| Argument | Description | Example |
|---|---|---|
startdate | An expression that can resolve to one of the following values: date, datetime, datetimeoffset, smalldatetime, time. Use four-digit years to avoid ambiguity. | 2007-05-05 12:10:09.3312722 |
enddate | An expression that can resolve to one of the following values: date, datetime, datetimeoffset, smalldatetime, time. Use four-digit years to avoid ambiguity. | 2007-05-04 12:10:09.3312722 |
datepart | The units in which DATEDIFF_BIG reports the difference between the startdate and enddate. Commonly used datepart units include month or second. | day |
This table lists all valid datepart argument names and abbreviations.
| datepart name |
|---|
| year |
| quarter |
| month |
| dayofyear |
| day |
| week |
| hour |
| minute |
| second |
| millisecond |
| microsecond |
| nanosecond |
Remarks
Use DATEDIFF_BIG in the SELECT <list>, WHERE, HAVING, GROUP BY and ORDER BY clauses.
DATEDIFF_BIG implicitly casts string literals as a datetime2 type. This means that DATEDIFF_BIG doesn't support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.
Specifying SET DATEFIRST has no effect on DATEDIFF_BIG. DATEDIFF_BIG always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.
DATEDIFF_BIG may overflow with a precision of nanosecond if the difference between enddate and startdate returns a value that's out of range for BigInt.
Example
This example uses different types of expressions as arguments for the startdate and enddate parameters. It calculates the number of day boundaries crossed between dates in two columns of a table.
SELECT
DATEDIFF_BIG(day, [Created], [Modified]) AS 'Duration'
FROM
[Cinchy].[Tables]
WHERE
[Deleted] IS NULL