Snowflake SQL Tutorial 72 - DATEADD, DATEDIFF, CURRENT_DATE - Dates 1

Snowflake SQL · Episode 72

In this post we are going to cover DATEADD, DATEDIFF and CURRENT_DATE in Snowflake.

DATEADD moves a date. We tell it a unit, how many of them, and what to move, and it hands back a new date. DATEDIFF goes the other way: we give it two dates and a unit, and it tells us how far apart they are. CURRENT_DATE and its relatives tell us what the clock says right now.

We will start by asking Snowflake what time it is, because there are several ways to ask and they do not all return the same thing. Then we will add days and months, and find out that adding a month twice is not the same as adding two months. Then we will measure gaps, and find out that DATEDIFF does not measure elapsed time at all. Every calculation here is anchored to DATE '2024-06-30' rather than to today, so your numbers will match these ones.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 72 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page
  1. Let's create some sample data
  2. Let's start by asking Snowflake what time it is
  3. Now let's move a date with DATEADD
  4. What happens at the end of a month?
  5. Is adding a month twice the same as adding two months?
  6. What happens if we add an hour to a DATE?
  7. Which typed literals can we actually write?
  8. Now let's measure a gap with DATEDIFF
  9. Let's run DATEDIFF over the customers table
  10. Let's finish with the edges, and one query that checks itself

Let's create some sample data

This is the first of five posts on dates. Everything here runs against the two tables the course has been using, ORDERS and CUSTOMERS.

Copy this into a Snowflake worksheet and run it once. The last line is the anchor date every calculation on this page uses.

Sample data — run once
-- ORDERS and CUSTOMERS come from the setup script this course uses
-- throughout. Run it once; every episode reuses the same tables.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

-- Every calculation on this page is anchored to one hard-coded date instead of
-- CURRENT_DATE, so the numbers you get are the numbers below. Only one grid on
-- this page reads the clock, and it is labelled where it appears.
SELECT DATE '2024-06-30' AS ANCHOR;

Let's look at ten of the orders, oldest first.

Query — the first ten orders by date
SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE, AMOUNT, STATUS
FROM ORDERS ORDER BY ORDER_DATE LIMIT 10;
Result10 rows · 5 columns
ORDER_IDCUSTOMER_IDORDER_DATEAMOUNTSTATUS
10011012023-01-124200.00SHIPPED
10021022023-01-28890.50SHIPPED
10031012023-02-146150.00SHIPPED
10041032023-02-271500.00CANCELLED
10051042023-03-092310.75SHIPPED
10061012023-03-225020.00SHIPPED
10071052023-04-057890.00SHIPPED
10081022023-04-191120.00SHIPPED
10091032023-05-023340.25SHIPPED
10101062023-05-21640.00PENDING

An order ID, a customer, a date, an amount and a status. There is more date range in the table than those ten rows show, so let's measure it.

Query — how much date range there is to work with
SELECT COUNT(*) AS ROWS_TOTAL,
       MIN(ORDER_DATE) AS EARLIEST,
       MAX(ORDER_DATE) AS LATEST,
       COUNT(DISTINCT ORDER_DATE) AS DISTINCT_DAYS
FROM ORDERS;
Result1 row · 4 columns
ROWS_TOTALEARLIESTLATESTDISTINCT_DAYS
412023-01-122024-11-0541

41 orders, running from 2023-01-12 to 2024-11-05. DISTINCT_DAYS is 41 as well, so no two orders share a day. That column comes back in Episode 74, where it decides whether a bucketing query groups anything at all.

Now the customers.

Query — customers and their signup dates
SELECT CUSTOMER_ID, CUSTOMER_NAME, COUNTRY, SEGMENT, SIGNUP_DATE
FROM CUSTOMERS ORDER BY SIGNUP_DATE;
Result10 rows · 5 columns
CUSTOMER_IDCUSTOMER_NAMECOUNTRYSEGMENTSIGNUP_DATE
101Northwind TradersUSAEnterprise2022-01-15
102Blue Ridge SupplyUSASMB2022-04-02
103Kestrel AnalyticsCanadaEnterprise2022-06-21
104Harbour FoodsCanadaSMB2023-02-08
105Alpine LogisticsNULLEnterprise2023-05-30
106Delta Print CoUSASMB2023-09-12
107Meridian HealthUSAEnterprise2024-01-07
108Copper Kettle LtdUKSMB2024-03-25
109Vantage MediaUKEnterprise2024-08-14
110Sable InteriorsUSASMB2024-11-03

Ten customers, each with a signup date. These are the dates we will measure against when we get to DATEDIFF.

The anchor

Every piece of arithmetic on this page uses one fixed date: DATE '2024-06-30'. A tutorial that does date maths against CURRENT_DATE is wrong by one day the following morning, and wrong by months by the time you read it. The current-time functions appear exactly once here, clearly labelled, and are never used in a calculation.

Let's start by asking Snowflake what time it is

There are several ways to ask, and they do not all return the same thing. Let's put them side by side.

Query — the only grid here whose values you will not match
SELECT CURRENT_DATE()          AS TODAY,
       CURRENT_TIMESTAMP()     AS NOW_,
       CURRENT_TIME()          AS TIME_OF_DAY,
       LOCALTIMESTAMP()        AS LOCAL_,
       SYSDATE()               AS SYSDATE_;
Result1 row · 5 columns
TODAYNOW_TIME_OF_DAYLOCAL_SYSDATE_
2026-09-252026-09-25 04:12:30.280000-07:0004:12:30.2800002026-09-25 04:12:30.280000-07:002026-09-25 11:12:30.280000

Your values will differ, and that is the entire point of these functions. What is worth reading is the shape of the row. NOW_ carries a UTC offset and SYSDATE_ does not, even though both were evaluated in the same statement.

Let's ask for the types, which explain why.

Query — TYPEOF on each one
SELECT TYPEOF(CURRENT_DATE()::VARIANT)      AS CURRENT_DATE_TYPE,
       TYPEOF(CURRENT_TIMESTAMP()::VARIANT) AS CURRENT_TIMESTAMP_TYPE,
       TYPEOF(SYSDATE()::VARIANT)           AS SYSDATE_TYPE,
       TYPEOF(LOCALTIMESTAMP()::VARIANT)    AS LOCALTIMESTAMP_TYPE;
Result1 row · 4 columns
CURRENT_DATE_TYPECURRENT_TIMESTAMP_TYPESYSDATE_TYPELOCALTIMESTAMP_TYPE
DATETIMESTAMP_LTZTIMESTAMP_NTZTIMESTAMP_LTZ
FunctionType it returnsWhat that means in practice
CURRENT_DATE()DATENo time component at all
CURRENT_TIMESTAMP()TIMESTAMP_LTZCarries a time zone offset; displayed in the session zone
LOCALTIMESTAMP()TIMESTAMP_LTZSame type as CURRENT_TIMESTAMP()
SYSDATE()TIMESTAMP_NTZNo offset stored; the instant is printed without one

So we have three ways to ask the same question and two different types, and a column that quietly loses its offset if we pick the wrong one. The difference between TIMESTAMP_LTZ, TIMESTAMP_NTZ and TIMESTAMP_TZ gets a post of its own at Episode 76. For now, notice only that SYSDATE() is not the same type as the other two.

Does the clock move while a query runs?

Worth knowing, because the answer decides whether a long query can contradict itself. Let's generate a thousand rows and count the distinct values.

Query — 1000 rows, how many distinct clock readings?
SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(DISTINCT CURRENT_TIMESTAMP()) AS DISTINCT_TIMESTAMPS,
       COUNT(DISTINCT CURRENT_DATE())      AS DISTINCT_DATES
FROM TABLE(GENERATOR(ROWCOUNT => 1000));
Result1 row · 3 columns
ROWS_TOTALDISTINCT_TIMESTAMPSDISTINCT_DATES
100011

One. The clock is read once per statement, not once per row. A query that runs for ten minutes cannot see the date change underneath it, so a batch job that stamps rows with CURRENT_TIMESTAMP() stamps them all identically. That is almost always what we want.

Now let's move a date with DATEADD

DATEADD takes three arguments in that order: which unit we are adding, how many of them, and what we are adding them to. A negative amount subtracts.

Query
SELECT DATE '2024-06-30'                        AS ANCHOR,
       DATEADD('day', 1, DATE '2024-06-30')     AS PLUS_1_DAY,
       DATEADD('day', -1, DATE '2024-06-30')    AS MINUS_1_DAY,
       DATEADD('month', 1, DATE '2024-06-30')   AS PLUS_1_MONTH,
       DATEADD('year', 1, DATE '2024-06-30')    AS PLUS_1_YEAR;
Result1 row · 5 columns
ANCHORPLUS_1_DAYMINUS_1_DAYPLUS_1_MONTHPLUS_1_YEAR
2024-06-302024-07-012024-06-292024-07-302025-06-30

Nothing surprising yet, and that is deliberate. The anchor is the 30th of a 30-day month, which is the easy case. We will move it to the 31st shortly and the behaviour changes.

The part names have short forms

Other people's code will use them, so they are worth recognising. 'day', 'dd', 'd' and 'days' are all the same part. Let's run all four, plus one real difference to compare against.

Query
SELECT DATEADD('day', 1, DATE '2024-06-30')     AS DAY_,
       DATEADD('dd', 1, DATE '2024-06-30')      AS DD,
       DATEADD('d', 1, DATE '2024-06-30')       AS D,
       DATEADD('days', 1, DATE '2024-06-30')    AS DAYS_,
       DATEADD('week', 1, DATE '2024-06-30')    AS WEEK_;
Result1 row · 5 columns
DAY_DDDDAYS_WEEK_
2024-07-012024-07-012024-07-012024-07-012024-07-07

DAY_, DD, D and DAYS_ are identical. WEEK_ is the only column that lands somewhere else, a week on from the anchor.

Now let's try it on a real date column

The same function over ORDER_DATE: a follow-up date thirty days out, the same date a month later, and the matching day a year earlier.

Query
SELECT ORDER_ID, ORDER_DATE,
       DATEADD('day', 30, ORDER_DATE)   AS FOLLOW_UP,
       DATEADD('month', 1, ORDER_DATE)  AS A_MONTH_LATER,
       DATEADD('year', -1, ORDER_DATE)  AS SAME_DAY_LAST_YEAR
FROM ORDERS
ORDER BY ORDER_ID
LIMIT 10;
Result10 rows · 5 columns
ORDER_IDORDER_DATEFOLLOW_UPA_MONTH_LATERSAME_DAY_LAST_YEAR
10012023-01-122023-02-112023-02-122022-01-12
10022023-01-282023-02-272023-02-282022-01-28
10032023-02-142023-03-162023-03-142022-02-14
10042023-02-272023-03-292023-03-272022-02-27
10052023-03-092023-04-082023-04-092022-03-09
10062023-03-222023-04-212023-04-222022-03-22
10072023-04-052023-05-052023-05-052022-04-05
10082023-04-192023-05-192023-05-192022-04-19
10092023-05-022023-06-012023-06-022022-05-02
10102023-05-212023-06-202023-06-212022-05-21

Compare FOLLOW_UP with A_MONTH_LATER row by row. They are not the same column, and they do not even disagree in a consistent direction.

  • Order 1001 — the month version is later than the thirty-day version, because the month it crosses is a long one.
  • Order 1003 — the month version is earlier, because the month it crosses is a short one.
  • Orders 1007 and 1008 — the two columns are identical, because the month they cross happens to be exactly thirty days long.
Days are not months

“A month” is not a fixed number of days, so DATEADD('day', 30, ...) and DATEADD('month', 1, ...) answer different questions. Pick the one your business rule actually means. A payment term of “net 30” is days; a subscription renewal is months.

What happens at the end of a month?

Here is the first thing that cannot work the way we might hope. Add one month to the 31st of January and there is no 31st of February to land on, so something has to give. Let's see what.

Query
SELECT DATEADD('month', 1, DATE '2024-01-31')  AS JAN_31_PLUS_1_MONTH,
       DATEADD('month', 1, DATE '2024-03-31')  AS MAR_31_PLUS_1_MONTH,
       DATEADD('month', 1, DATE '2023-01-31')  AS JAN_31_2023_NON_LEAP,
       DATEADD('month', -1, DATE '2024-03-31') AS MAR_31_MINUS_1_MONTH;
Result1 row · 4 columns
JAN_31_PLUS_1_MONTHMAR_31_PLUS_1_MONTHJAN_31_2023_NON_LEAPMAR_31_MINUS_1_MONTH
2024-02-292024-04-302023-02-282024-02-29

Read those four columns carefully, because each one is a separate rule.

  • 2024-01-31 plus a month gives 2024-02-29 — it clamps to the last day of the shorter month rather than overflowing into March.
  • 2024-03-31 plus a month gives 2024-04-30 — same clamp, different month length.
  • 2023-01-31 plus a month gives 2023-02-28. Same sum, different year, different answer: 2024 is a leap year and 2023 is not.
  • The clamp applies going backwards too: 2024-03-31 minus a month is 2024-02-29.

That behaviour is reasonable. It is also the mechanism behind the trap we are about to walk into.

Let's try the leap day

Query
SELECT DATEADD('year', 1, DATE '2024-02-29')  AS LEAP_DAY_PLUS_A_YEAR,
       DATEADD('year', 4, DATE '2024-02-29')  AS PLUS_FOUR_YEARS,
       DATEADD('day', 1, DATE '2024-02-28')   AS DAY_AFTER_FEB_28_2024,
       DATEADD('day', 1, DATE '2023-02-28')   AS DAY_AFTER_FEB_28_2023;
Result1 row · 4 columns
LEAP_DAY_PLUS_A_YEARPLUS_FOUR_YEARSDAY_AFTER_FEB_28_2024DAY_AFTER_FEB_28_2023
2025-02-282028-02-292024-02-292023-03-01

2024-02-29 plus a year is 2025-02-28, which is the same clamp applied to a year instead of a month. Plus four years gets us a real 29th of February again.

DAY_AFTER_FEB_28_2024 and DAY_AFTER_FEB_28_2023 show the same fact from the other side. The day after 2024-02-28 is the leap day, and the day after 2023-02-28 is the 1st of March.

Anniversaries on the leap day

If you store an anniversary — a renewal date, a birthday, a contract date — and it falls on the 29th of February, it will be the 28th in three years out of four and you cannot get the 29th back by adding years. Decide which day you mean and store that.

Is adding a month twice the same as adding two months?

This is the result the video says it did not expect before running it, and it follows directly from the clamp. Let's start from the same date, add a month twice, then add two months in one step, and compare.

Query
SELECT DATEADD('month', 1, DATEADD('month', 1, DATE '2024-01-31')) AS TWICE_ONE_MONTH,
       DATEADD('month', 2, DATE '2024-01-31')                      AS ONCE_TWO_MONTHS,
       DATEADD('month', 1, DATEADD('month', 1, DATE '2024-01-31'))
         = DATEADD('month', 2, DATE '2024-01-31')                  AS SAME;
Result1 row · 3 columns
TWICE_ONE_MONTHONCE_TWO_MONTHSSAME
2024-03-292024-03-31FALSE

2024-03-29 and 2024-03-31. SAME says FALSE. Month arithmetic in Snowflake is not associative, and the clamp is why.

  1. 2024-01-31 plus a month clamps to 2024-02-29.
  2. The 31st is now gone. It is not remembered as an intention, only as a date.
  3. A month after 2024-02-29 is 2024-03-29.
  4. Done in one step, Snowflake works from the original 31st and lands on 2024-03-31.
What this does to a billing job

A monthly schedule written as “add one month each time” drifts off month-end permanently. The first short month pulls it back and nothing ever pushes it forward again. Compute each occurrence from the original date (DATEADD('month', n, start_date)), or derive month-end explicitly. LAST_DAY, which is in Episode 73, is the honest way to say “the end of the month”.

What happens if we add an hour to a DATE?

A DATE has no time component, so there is nowhere to put an hour. Snowflake does not refuse and does not round. Let's see what it does instead.

Query
SELECT DATEADD('hour', 1, DATE '2024-06-30')                   AS DATE_PLUS_AN_HOUR,
       TYPEOF(DATEADD('hour', 1, DATE '2024-06-30')::VARIANT)  AS ITS_TYPE,
       DATEADD('hour', 25, DATE '2024-06-30')                  AS PLUS_25_HOURS;
Result1 row · 3 columns
DATE_PLUS_AN_HOURITS_TYPEPLUS_25_HOURS
2024-06-30 01:00:00TIMESTAMP_NTZ2024-07-01 01:00:00

ITS_TYPE says TIMESTAMP_NTZ. The expression went in as a DATE and came out as a timestamp, with the time set to midnight plus the hour we asked for. Add 25 hours and the date rolls as well, which is the behaviour we want.

A type change you did not ask for

This is quiet. A column defined as DATE, fed through DATEADD('hour', ...), arrives downstream as a TIMESTAMP_NTZ — and an equality join against a real DATE column then compares midnight-plus-an-hour against midnight. Nothing errors; rows just stop matching.

Now let's try it where the time already exists

Query
SELECT '2024-06-30 23:30:00'::TIMESTAMP_NTZ                          AS ANCHOR,
       DATEADD('hour', 1, '2024-06-30 23:30:00'::TIMESTAMP_NTZ)      AS PLUS_1_HOUR,
       DATEADD('minute', 45, '2024-06-30 23:30:00'::TIMESTAMP_NTZ)   AS PLUS_45_MIN,
       DATEADD('second', 1, '2024-06-30 23:59:59'::TIMESTAMP_NTZ)    AS PLUS_1_SEC;
Result1 row · 4 columns
ANCHORPLUS_1_HOURPLUS_45_MINPLUS_1_SEC
2024-06-30 23:30:002024-07-01 00:30:002024-07-01 00:15:002024-07-01 00:00:00

PLUS_1_HOUR, PLUS_45_MIN and PLUS_1_SEC all cross midnight, and the date rolls with them. DATEADD on a timestamp is ordinary clock arithmetic. No clamping and no surprises, because hours and minutes are all the same length as each other in a way that months are not.

Which typed literals can we actually write?

A short detour, and a genuinely useful one, because the pattern is not the one we would guess. Writing a type name in front of a string literal works for DATE, which is why every query on this page does it.

Query — four spellings that work
SELECT DATE '2024-06-30'                        AS DATE_LITERAL_WORKS,
       '2024-06-30'::DATE                       AS AND_SO_DOES_THE_CAST,
       '2024-06-30 23:30:00'::TIMESTAMP_NTZ     AS TIMESTAMP_NEEDS_THE_CAST,
       TO_TIMESTAMP_NTZ('2024-06-30 23:30:00')  AS OR_THE_FUNCTION;
Result1 row · 4 columns
DATE_LITERAL_WORKSAND_SO_DOES_THE_CASTTIMESTAMP_NEEDS_THE_CASTOR_THE_FUNCTION
2024-06-302024-06-302024-06-30 23:30:002024-06-30 23:30:00

A DATE literal, a cast to DATE, a cast to TIMESTAMP_NTZ and TO_TIMESTAMP_NTZ(). All four parse.

So the obvious next step is to write the type name in front of a timestamp string the same way.

Query — TIMESTAMP_NTZ 'literal'
SELECT TIMESTAMP_NTZ '2024-06-30 23:30:00' AS SHOULD_NOT_GET_THIS_FAR;
Query failed — Snowflake returned an error
001003 (42000): SQL compilation error:
syntax error line 1 at position 21 unexpected ''2024-06-30 23:30:00''.

A compilation error, pointing at the string. TIMESTAMP_NTZ does not take a typed literal. The surprise is not that one spelling fails. It is which spelling works instead.

Query — TIMESTAMP 'literal'
SELECT TIMESTAMP '2024-06-30 23:30:00'          AS PLAIN_TIMESTAMP_LITERAL,
       TYPEOF((TIMESTAMP '2024-06-30 23:30:00')::VARIANT) AS ITS_TYPE;
Result1 row · 2 columns
PLAIN_TIMESTAMP_LITERALITS_TYPE
2024-06-30 23:30:00TIMESTAMP_NTZ

The plain, unqualified keyword parses, and ITS_TYPE reports TIMESTAMP_NTZ — the exact type the qualified spelling refused to produce.

SpellingResult
DATE '2024-06-30'Parses, gives a DATE
TIMESTAMP '2024-06-30 23:30:00'Parses, gives a TIMESTAMP_NTZ
TIMESTAMP_NTZ '2024-06-30 23:30:00'Syntax error
'2024-06-30 23:30:00'::TIMESTAMP_NTZParses
TO_TIMESTAMP_NTZ('2024-06-30 23:30:00')Parses
Worth bookmarking rather than deriving

There is no way to reason your way to that table, which is the only reason it is on the page. Use the cast or TO_TIMESTAMP_NTZ() and the question never comes up.

Now let's measure a gap with DATEDIFF

DATEDIFF takes a part, a start and an end, and returns how far apart they are in that unit. The word “apart” is doing a lot of work in that sentence. Let's find out how much.

Query
SELECT DATEDIFF('year',  DATE '2023-12-31', DATE '2024-01-01') AS ONE_DAY_ONE_YEAR,
       DATEDIFF('month', DATE '2024-01-31', DATE '2024-02-01') AS ONE_DAY_ONE_MONTH,
       DATEDIFF('day',   DATE '2023-12-31', DATE '2024-01-01') AS ONE_DAY_ONE_DAY,
       DATEDIFF('year',  DATE '2024-01-01', DATE '2024-12-31') AS ALMOST_A_YEAR_APART;
Result1 row · 4 columns
ONE_DAY_ONE_YEARONE_DAY_ONE_MONTHONE_DAY_ONE_DAYALMOST_A_YEAR_APART
1110

Read ONE_DAY_ONE_YEAR and ALMOST_A_YEAR_APART together. 2023-12-31 to 2024-01-01 is one day apart, which ONE_DAY_ONE_DAY confirms, and DATEDIFF('year', ...) reports 1. Meanwhile 2024-01-01 to 2024-12-31, nearly the whole of one calendar year, reports 0.

DATEDIFF is not measuring elapsed time. It is counting how many boundaries of that unit lie between the two dates. One new-year midnight was crossed in the first case and none in the second. ONE_DAY_ONE_MONTH makes the same point one unit down: the 31st of January to the 1st of February is one month, because one month boundary was crossed.

Which is why it is the wrong way to compute an age

Let's take somebody born on 2000-12-31, measure them at 2024-01-01, and ask two different questions about the same pair of dates.

Query
SELECT DATEDIFF('year', DATE '2000-12-31', DATE '2024-01-01')  AS DATEDIFF_SAYS,
       FLOOR(DATEDIFF('day', DATE '2000-12-31', DATE '2024-01-01') / 365.25)
         AS WHOLE_YEARS_APPROX,
       DATEDIFF('day', DATE '2000-12-31', DATE '2024-01-01')   AS DAYS_APART;
Result1 row · 3 columns
DATEDIFF_SAYSWHOLE_YEARS_APPROXDAYS_APART
24238401

DATEDIFF_SAYS is 24. WHOLE_YEARS_APPROX, which is the day count divided by 365.25, is 23. The second one is their age, and DAYS_APART says they are 8401 days old. If you need whole completed years, count days or compare month-and-day. Do not ask DATEDIFF for years.

Now let's check the argument order

Query
SELECT DATEDIFF('day', DATE '2024-01-01', DATE '2024-01-31') AS START_THEN_END,
       DATEDIFF('day', DATE '2024-01-31', DATE '2024-01-01') AS END_THEN_START,
       DATEDIFF('day', DATE '2024-01-01', DATE '2024-01-01') AS SAME_DAY;
Result1 row · 3 columns
START_THEN_ENDEND_THEN_STARTSAME_DAY
30-300

Start first, then end. Swap them and we get the negative, not an error, so a reversed argument list produces a report full of negative numbers rather than a failure anybody would notice. Two identical dates give 0.

And here is one pair of dates at five different granularities.

Query
SELECT DATEDIFF('year',    DATE '2022-01-15', DATE '2024-06-30') AS YEARS_,
       DATEDIFF('quarter', DATE '2022-01-15', DATE '2024-06-30') AS QUARTERS_,
       DATEDIFF('month',   DATE '2022-01-15', DATE '2024-06-30') AS MONTHS_,
       DATEDIFF('week',    DATE '2022-01-15', DATE '2024-06-30') AS WEEKS_,
       DATEDIFF('day',     DATE '2022-01-15', DATE '2024-06-30') AS DAYS_;
Result1 row · 5 columns
YEARS_QUARTERS_MONTHS_WEEKS_DAYS_
2929128897

2 years, 9 quarters, 29 months, 128 weeks, 897 days. Every one of those is a correct answer to a different question, which is exactly why the part argument deserves a moment's thought rather than a habit.

Let's run DATEDIFF over the customers table

Now the real data, measured from each signup date to DATE '2024-06-30', at three granularities at once.

Query
SELECT CUSTOMER_ID, CUSTOMER_NAME, SIGNUP_DATE,
       DATEDIFF('day',   SIGNUP_DATE, DATE '2024-06-30') AS DAYS_TO_ANCHOR,
       DATEDIFF('month', SIGNUP_DATE, DATE '2024-06-30') AS MONTHS_TO_ANCHOR,
       DATEDIFF('year',  SIGNUP_DATE, DATE '2024-06-30') AS YEARS_TO_ANCHOR
FROM CUSTOMERS
ORDER BY SIGNUP_DATE;
Result10 rows · 6 columns · scroll →
CUSTOMER_IDCUSTOMER_NAMESIGNUP_DATEDAYS_TO_ANCHORMONTHS_TO_ANCHORYEARS_TO_ANCHOR
101Northwind Traders2022-01-15897292
102Blue Ridge Supply2022-04-02820262
103Kestrel Analytics2022-06-21740242
104Harbour Foods2023-02-08508161
105Alpine Logistics2023-05-30397131
106Delta Print Co2023-09-1229291
107Meridian Health2024-01-0717550
108Copper Kettle Ltd2024-03-259730
109Vantage Media2024-08-14-45-20
110Sable Interiors2024-11-03-126-50

Northwind Traders signed up on 2022-01-15, which is the pair of dates we just measured at every granularity, so 897 and 29 should look familiar.

Vantage Media and Sable Interiors come back negative in DAYS_TO_ANCHOR and MONTHS_TO_ANCHOR, and they are negative honestly: those two customers signed up after the anchor. The data runs into November 2024 and the anchor is June, so from the anchor's point of view they are in the future.

Where does the year count disagree with the elapsed years?

We have been saying that DATEDIFF('year', ...) is not elapsed years. Rather than assert it, let's ask the table for every row where the two methods differ.

Query
SELECT CUSTOMER_ID, SIGNUP_DATE,
       DATEDIFF('year', SIGNUP_DATE, DATE '2024-06-30')  AS DATEDIFF_YEARS,
       FLOOR(DATEDIFF('day', SIGNUP_DATE, DATE '2024-06-30') / 365.25) AS ELAPSED_YEARS
FROM CUSTOMERS
WHERE DATEDIFF('year', SIGNUP_DATE, DATE '2024-06-30')
      <> FLOOR(DATEDIFF('day', SIGNUP_DATE, DATE '2024-06-30') / 365.25)
ORDER BY CUSTOMER_ID;
Result3 rows · 4 columns
CUSTOMER_IDSIGNUP_DATEDATEDIFF_YEARSELAPSED_YEARS
1062023-09-1210
1092024-08-140-1
1102024-11-030-1

Three rows. Customer 106 is the real lesson: DATEDIFF says a year has passed since 2023-09-12 and the elapsed count says none has.

Customers 109 and 110 are the two future signups again. -1 elapsed against 0 from DATEDIFF is a disagreement about how to round a negative rather than about the calendar.

Let's ask the same question as a single count.

Query — the same question as a single count
SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(DATEDIFF('year', SIGNUP_DATE, DATE '2024-06-30')
                <> FLOOR(DATEDIFF('day', SIGNUP_DATE, DATE '2024-06-30') / 365.25))
         AS DISAGREE
FROM CUSTOMERS;
Result1 row · 2 columns
ROWS_TOTALDISAGREE
103

3 out of 10. On a ten-row table, nearly a third of the rows would carry the wrong year count, and every one of them would look perfectly plausible in a report.

Why the future rows were not hidden

Those two negative rows could have been filtered out of this page and nobody would have known. They are left in because they are what a fixed anchor means: it is a point in time, and some of your data is later than it. A report with an as-of date has exactly the same property.

Let's finish with the edges, and one query that checks itself

Two edges worth knowing, both in one row.

Query
SELECT DATEDIFF('day', NULL, DATE '2024-06-30')       AS START_IS_NULL,
       DATEDIFF('day', DATE '2024-06-30', NULL)       AS END_IS_NULL,
       DATE '2024-06-30' - DATE '2024-01-01'          AS MINUS_OPERATOR,
       DATEDIFF('day', DATE '2024-01-01', DATE '2024-06-30') AS DATEDIFF_FOR_COMPARISON;
Result1 row · 4 columns
START_IS_NULLEND_IS_NULLMINUS_OPERATORDATEDIFF_FOR_COMPARISON
NULLNULL181181

A NULL on either side gives NULL, not zero and not an error. That is right, and it means a DATEDIFF over a nullable date column will produce NULLs we have to handle.

MINUS_OPERATOR and DATEDIFF_FOR_COMPARISON are the other edge. We can subtract one DATE from another with a plain minus sign, and it gives days. Both columns say 181.

Which of the two to write

The minus operator is shorter and only ever gives you days. DATEDIFF('day', ...) spells out which unit it means and reads the same as the rest of your date code. Prefer it on anything someone else will maintain.

Let's put both functions in one query

DATEADD for a due date, DATEDIFF for an age in two units, and then the two functions composed so that the answer has to come back to the anchor.

Query
SELECT ORDER_ID, ORDER_DATE,
       DATEADD('day', 30, ORDER_DATE)                     AS DUE,
       DATEDIFF('day', ORDER_DATE, DATE '2024-06-30')     AS AGE_IN_DAYS,
       DATEDIFF('month', ORDER_DATE, DATE '2024-06-30')   AS AGE_IN_MONTHS,
       DATEADD('day', DATEDIFF('day', ORDER_DATE, DATE '2024-06-30'), ORDER_DATE)
         AS ROUND_TRIP_SHOULD_BE_THE_ANCHOR
FROM ORDERS
ORDER BY ORDER_ID
LIMIT 5;
Result5 rows · 6 columns · scroll →
ORDER_IDORDER_DATEDUEAGE_IN_DAYSAGE_IN_MONTHSROUND_TRIP_SHOULD_BE_THE_ANCHOR
10012023-01-122023-02-11535172024-06-30
10022023-01-282023-02-27519172024-06-30
10032023-02-142023-03-16502162024-06-30
10042023-02-272023-03-29489162024-06-30
10052023-03-092023-04-08479152024-06-30

ROUND_TRIP_SHOULD_BE_THE_ANCHOR is the check. Add the measured day difference back onto the order date and we land on 2024-06-30 on every row. In days, DATEADD and DATEDIFF are exact inverses. In months they are not, for exactly the reason the clamp showed us, and that is the one thing from this post worth carrying into the next one.

Key points

  • DATEADD(part, amount, date) adds; a negative amount subtracts. The part names have short forms — 'day', 'dd', 'd' and 'days' are the same part.
  • Month arithmetic clamps: 2024-01-31 plus a month is 2024-02-29, and the same sum in 2023 gives 2023-02-28.
  • Adding a month twice is not adding two months. The clamp throws away the original day of month, so the comparison column reads FALSE — and a schedule written as “+1 month each time” drifts off month-end for good.
  • Adding an hour to a DATE promotes the result to TIMESTAMP_NTZ. The type changes without a warning.
  • DATE 'literal' and TIMESTAMP 'literal' both parse, and TIMESTAMP 'literal' yields a TIMESTAMP_NTZ — but TIMESTAMP_NTZ 'literal' is a syntax error. Use a cast or TO_TIMESTAMP_NTZ().
  • DATEDIFF counts boundary crossings, not elapsed time. One day either side of new year is “1 year”; nearly all of one calendar year is “0”.
  • So DATEDIFF('year', ...) is the wrong way to compute an age or a tenure — it disagreed with elapsed years on 3 of the 10 customers here.
  • Argument order is start then end. Reversed, you get a negative number rather than an error.
  • A NULL on either side returns NULL, and date - date returns days, the same as DATEDIFF('day', ...).
  • CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP and SYSDATE do not all return the same type, and the clock is read once per statement — 1000 rows produced one distinct timestamp.
Next in this series

Episode 73 — pulling dates apart. DATE_TRUNC, DATE_PART, EXTRACT, LAST_DAY and NEXT_DAY — including a monthly report that runs clean, looks entirely reasonable and silently adds two years together.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, including the error pane. The ORDERS and CUSTOMERS data is invented demo data created by that script. Every calculation is anchored to DATE '2024-06-30' rather than the clock, so these values do not go stale; the one grid that does read the clock is labelled as such and is not used in any calculation. No performance or benchmark claims are made anywhere in this series. Function behaviour follows the Snowflake date & time functions documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)