Return Date and Time difference values
Last updated
Last updated
The return date and time difference value functions covered in this section are:
The DATDIFF
function 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.
datepart
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.
startdate
An expression that can resolve to one of the following values:
date
datetime
datetimeoffset
smalldatetime
time
Use four-digit years to avoid ambiguity.
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.
Finding the number of days between two dates.
Specifying user-defined variables for startdate and enddate
Example 3: Specifying scalar system functions for startdate and enddate
`DATEIFF_BIG function 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.
datepart
The part of startdate and enddate that specifies the type of boundary crossed.
This table lists all valid datepart argument names and abbreviations.
startdate
An expression that can resolve to one of the following values:
date
datetime
datetimeoffset
smalldatetime
time
For date, DATEDIFF_BIG
will accept a column expression, expression, string literal, or user-defined variable. A string literal value must resolve to a datetime. Use four-digit years to avoid ambiguity issues. DATEDIFF_BIG
subtracts startdate from enddate. To avoid ambiguity, use four-digit years.
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.
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.
datepart
year
quarter
month
dayofyear
day
week
hour
minute
second
millisecond
microsecond
nanosecond
datepart name
year
quarter
month
dayofyear
day
week
hour
minute
second
millisecond
microsecond
nanosecond