DATEDIFF

The DATEDIFF function calculates the difference between two timestamps as measured in datePart units. That is, the number of milliseconds, days, years, and so on in a given interval between two timestamps. See Expressions and Literals.

Syntax

dateDIFF
(
datePart LITERAL
startTimestamp TIMESTAMP,
endTimestamp TIMESTAMP
)

datePart is an “extended time-unit literal”, and can be any of the following: YEAR, QUARTER, MONTH, DAY, WEEK, HOUR, MINUTE, SECOND, or MILLISECOND.

These are literal constants, not strings, so no quotes are used.

Returns As a BIGINT, the number of time-unit boundaries of the specified kind that fall between the start point and the end point. If the start point is later that the end point, the result can be negative. Returns NULL if either T1 or T2 is null.

For example: DATEDIFF(YEAR, T1, T2) = 0 means that T1 and T2 occur in the same year. DATEDIFF(MONTH, T1, T2) = 0 means that T1 and T2 occur in the same month. DATEDIFF(WEEK, T1, T2) = 0 means that T1 and T2 occur in the same week. DATEDIFF(WEEK, T1, T2) = 1 means that T1 is in the week after T2.

Examples

There is one year difference betweem the following timestamps:

values(datediff(year,timestamp '2021-12-31 23:59:59.999', timestamp '2022-01-01 00:00:00.001'));
'EXPR$0'
'1'   

There are 79 quarters between the two dates given here; the second timestamp is earlier than the first:

values(datediff(quarter, timestamp '2021-12-31 23:59:59.999', timestamp '2002-01-01 00:00:00.001'));
'EXPR$0'
'-79'