Date functions

DateDiff

View as Markdown

The DateDiff function calculates the time difference between two dates.

Syntax

DateDiff(unit, start, end)

The function has the following arguments:

unit
Required
The unit to measure the difference.
Can be one of “year”, “quarter”, “month”, “week”, “day”, “hour”, “minute”, “second”, “millisecond”.
start
Required
The starting date
Note that the value must be a date. If the column is not in the appropriate format, use the Date function on the argument.
end
Required
The ending date
Note that the value must be a date. If the column is not in the appropriate format, use the Date function on the argument.

Notes

  • To find the difference, the start date is subtracted from the end date. If the end date precedes the start date, the output is a negative integer.
  • The unit argument does not support columns.
  • Sigma rounds the result to the nearest integer.
  • If start or end is Null, the function returns Null.

Examples

DateDiff("day", Date("2022-01-01"), Date("2023-01-01"))

Finds the difference between the dates January 1, 2022 and January 1, 2023, and returns 365.

DateDiff("day", Date("2024-01-01"), Date("2023-01-01"))

Finds the difference between the dates January 1, 2024 and January 1, 2023, and returns -365.

DateDiff("day", Date("2024-01-01"), Null)

Returns Null.

DateDiff(“day”, [Invoice Date], Today())

Returns the number of days between the date in the Invoice Date column and the current UTC date.

DateDiff("year", [Invoice Date], Date("2018-01-01"))

Returns the number of years between the date in the Invoice Date column and January 1, 2018.