How to Use DATEDIFF in Snowflake

Galaxy Glossary

How do I calculate the difference between two dates in Snowflake?

DATEDIFF returns the number of specified date or time parts between two dates or timestamps.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does Snowflake DATEDIFF do?

DATEDIFF counts how many complete date or time parts (days, weeks, months, etc.) exist between a start and end value. It supports DATE, TIME, TIMESTAMP, and TIMESTAMP_TZ types.

What is the syntax of DATEDIFF?

DATEDIFF( <date_or_time_part>, <start_expr>, <end_expr> )

<date_or_time_part> can be year, quarter, month, week, day, hour, minute, second, etc. start_expr and end_expr are the two dates or timestamps you want to compare.

How do I get days between customer sign-up and first order?

SELECT c.id, DATEDIFF(day, c.created_at, MIN(o.order_date)) AS days_to_first_order FROM Customers c JOIN Orders o ON o.customer_id = c.id GROUP BY c.id;

The query returns the number of days each customer waited before placing their first order.

Can DATEDIFF calculate months between orders?

SELECT o1.id AS first_order, o2.id AS second_order, DATEDIFF(month, o1.order_date, o2.order_date) AS months_between FROM Orders o1 JOIN Orders o2 ON o2.customer_id = o1.customer_id WHERE o2.order_date > o1.order_date;

This pattern finds months between any two orders from the same customer.

Does DATEDIFF consider time zones?

For TIMESTAMP_TZ values, Snowflake converts both inputs to UTC before calculation, ensuring accurate differences across zones.

Best practices for DATEDIFF

Store dates in DATE and timestamps in TIMESTAMP_TZ to avoid implicit casts.
Use consistent time parts; mixing day and hour parts may confuse readers.
Filter data first; calculating differences on millions of rows can be expensive.

Common mistakes

Reversing start and end dates

DATEDIFF returns negative numbers if start_expr > end_expr. Swap arguments or wrap with ABS() when you only need magnitude.

Ignoring partial periods

DATEDIFF counts whole units only. To include partial days, switch to TIMEDIFF or subtract timestamps and divide by seconds.

Related functions

Use DATEADD to shift dates forward or backward, and DATEDIFF together for cohort analysis.

Why How to Use DATEDIFF in Snowflake is important

How to Use DATEDIFF in Snowflake Example Usage


SELECT p.name,
       SUM(oi.quantity)                          AS units_sold,
       DATEDIFF(day, MIN(o.order_date), MAX(o.order_date)) AS sales_span_days
FROM   Products    p
JOIN   OrderItems  oi ON oi.product_id = p.id
JOIN   Orders      o  ON o.id = oi.order_id
GROUP  BY p.name;

How to Use DATEDIFF in Snowflake Syntax


DATEDIFF(<date_or_time_part>, <start_expr>, <end_expr>)
-- Examples
-- Days between customer sign-up and today
DATEDIFF(day, created_at, CURRENT_DATE);

-- Months between first and last order for a customer
DATEDIFF(month, MIN(order_date), MAX(order_date));

Common Mistakes

Supplying start and end dates in the wrong order: returns negative results. Fix by placing the earlier date first or wrapping with ABS().
Expecting partial periods: DATEDIFF counts whole units only. Use TIMESTAMP subtraction for precise fractional differences.