1. DATEDIFF Function in Snowflake
The DATEDIFF function in Snowflake facilitates the calculation of the difference between two date, time, or timestamp values. The function allows for determining the number of years, months, or days between two dates, as well as the number of hours, minutes, or seconds between two timestamp values.
Snowflake also supports TIMEDIFF and TIMESTAMPDIFF functions to calculate the difference between two date, time, or timestamp values which work exactly like the DATEDIFF function.
2. Syntax of DATEDIFF Function in Snowflake
The following is the syntax of the DATEDIFF Function in Snowflake.
DATEDIFF( Date_or_Time_Part, Date_or_Time_Value_1, Date_or_Time_Value_2)Date_or_Time_Part
It represents the unit of time, based on which the difference calculation should be performed. The following are the supported date/time part values in the DATEDIFF function.
| Type | Supported Date_or_Time_Part Values |
| Date value | YEAR, QUARTER, MONTH, WEEK, DAY |
| Time value | HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND |
Date_or_Time_Value_1
The date or time value from which you wish to calculate the difference.
Date_or_Time_Value_2
The date or time value until which you aim to calculate the difference.
3. How to Calculate the Number of Years Between Two Dates in Snowflake?
The following examples calculate the number of years using the DATEDIFF function between two dates, 1st JAN 2023 and 1st JAN 2024.
SELECT DATEDIFF(YEAR, '2023-01-01', '2024-01-01') AS YEAR_DIFF;
/** Returns 1 **/
4. How to Calculate the Number of Months Between Two Dates in Snowflake?
The following examples calculate the number of months using the DATEDIFF function between two dates, 1st JAN 2023 and 1st JAN 2024.
SELECT DATEDIFF(MONTH, '2023-01-01', '2024-01-01') AS MONTH_DIFF;
/** Returns 12 **/
5. How to Calculate the Number of Days Between Two Dates in Snowflake?
The following examples calculate the number of days using the DATEDIFF function between two dates, 1st JAN 2023 and 1st JAN 2024.
SELECT DATEDIFF(DAY, '2023-01-01', '2024-01-01') AS DAY_DIFF;
/** Returns 365 **/
The Minus sign (-) can also be used to subtract the dates to extract the number of days between them as shown below.
SELECT '2023-01-01' DATE_1, '2024-01-01' DATE_2,
('2024-01-01'::DATE - '2023-01-01'::DATE) AS DAY_DIFF;
/** Returns 365 **/
Note that when using the minus sign to calculate the number of days between two dates, pass the latest date as the first argument and the oldest date as the second argument. This is contrary to the order in which arguments are passed to the DATEDIFF function.
6. How to Calculate the Number of Hours Between Two Timestamp values in Snowflake?
The following examples calculate the number of hours between two timestamp values using the DATEDIFF function.
SELECT DATEDIFF(HOUR, '2024-01-01 00:00:00', '2024-01-02 00:00:00') AS HOUR_DIFF;
/** Returns 24 **/
7. How to Calculate the Number of Minutes Between Two Timestamp values in Snowflake?
The following examples calculate the number of minutes between two timestamp values using the DATEDIFF function.
SELECT DATEDIFF(MINUTE, '2024-01-01 00:00:00', '2024-01-02 00:00:00') AS MINUTE_DIFF;
/** Returns 1440 **/
8. How to Calculate the Number of Seconds Between Two Timestamp values in Snowflake?
The following examples calculate the number of seconds between two timestamp values using the DATEDIFF function.
SELECT DATEDIFF(SECOND, '2024-01-01 00:00:00.000', '2024-01-02 00:00:00') AS SECOND_DIFF;
/** Returns 86400 **/
9. DATEDIFF Behaviour: Truncation vs Rounding the Date Time Values
The DATEDIFF function truncates the date time values rather than rounding them to the nearest value.
The following example demonstrates how DATEDIFF functions truncate the date values rather than rounding them to get the nearest value.
SELECT COLUMN1 DATE_1, COLUMN2 DATE_2,
DATEDIFF(YEAR, COLUMN1, COLUMN2) YEAR_DIFF,
DATEDIFF(MONTH, COLUMN1, COLUMN2) MONTH_DIFF,
DATEDIFF(DAY, COLUMN1, COLUMN2) DAY_DIFF
FROM VALUES
('2023-01-01', '2024-01-01'),
('2023-01-01', '2023-12-31'),
('2023-01-01 00:00:00', '2023-02-01 00:00:00'),
('2023-01-01 00:00:00', '2023-01-31 23:59:59');
In the above example,
- The year difference between January 1st 2023 and December 31st 2023 is calculated as 0 years rather than considering it as 1 complete year. It is only when the range is extended until January 1st 2024, the difference is calculated as 1 complete year.
- Similarly, the calculation of the number of months between January 1st 2023, and December 31st 2023, results in 11 months instead of 12 months. It is only when the range is extended until January 1st 2024, the difference is calculated as 12 months.
- The same logic applies in the calculation of the number of days between two date values.
The following example demonstrates how DATEDIFF functions truncate the times values rather than rounding them to get the nearest value.
SELECT COLUMN1 TIMESTAMP_1, COLUMN2 TIMESTAMP_2,
DATEDIFF(HOUR, COLUMN1, COLUMN2) HOUR_DIFF,
DATEDIFF(MINUTE, COLUMN1, COLUMN2) MINUTE_DIFF,
DATEDIFF(SECOND, COLUMN1, COLUMN2) SECOND_DIFF
FROM VALUES
('2024-01-01 01:00:00', '2024-01-01 01:59:00'),
('2024-01-01 01:00:00', '2024-01-01 01:59:59'),
('2024-01-01 01:00:00', '2024-01-01 02:00:00')
;
In the above example, we have calculated the hour, minute and second difference between 1:00 hour and 1:59, 1:59:59, 02:00 hours. You can observe how the DATEDIFF function truncates the timestamp values in the result.
Subscribe to our Newsletter !!
Related Articles:
The GROUP BY ALL clause is similar to GROUP BY clause except that it automatically include all non-aggregate items in the SELECT clause for grouping.
QUALIFY clause in a SELECT statement allows you to filter query results of Window functions within the same query.
Snowflake Dynamic Tables track the changes in the query data specified and refresh the materialized results incrementally through an automated process.
