Snowflake SQL Tutorial 73 - DATE_TRUNC, DATE_PART, EXTRACT, LAST_DAY, NEXT_DAY - Dates 2

Snowflake SQL · Episode 73

In this post we are going to cover DATE_TRUNC, DATE_PART, EXTRACT, LAST_DAY and NEXT_DAY in Snowflake.

These are the functions that take a date apart. DATE_TRUNC flattens a date down to the start of a period. DATE_PART and EXTRACT pull one number out of it. LAST_DAY and NEXT_DAY find you another date relative to the one you handed them.

We will start with DATE_TRUNC and build it up step by step. On the way we will also cover the week, which Snowflake disagrees with itself about, and a monthly report that runs without an error, looks completely reasonable, and adds two years together.

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

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

On this page
  1. Let's create some sample data
  2. Let's start with DATE_TRUNC
  3. Now let's try DATE_PART
  4. Zero for which day? Let's number a whole week
  5. What happens at the end of a year?
  6. So what is the real difference between the two?
  7. Now let's group some orders by month
  8. Let's try LAST_DAY
  9. Now let's try NEXT_DAY and PREVIOUS_DAY
  10. One last thing: one row of a date dimension

Let's create some sample data

This is the second of five posts on dates, and it runs against the same two tables as the rest of the course. The script below sets up the anchor date that every calculation on this page is measured from.

Copy it into a Snowflake worksheet and run it once.

Sample data
-- 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 fixed date instead of
-- CURRENT_DATE, so nothing here reads the clock and nothing goes stale.
SELECT DATE '2024-06-30' AS ANCHOR;

Here are the orders we will be pulling apart.

Query
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

And the customers, which we will use once or twice for a second date column.

Query
SELECT CUSTOMER_ID, CUSTOMER_NAME, COUNTRY, SIGNUP_DATE
FROM CUSTOMERS ORDER BY SIGNUP_DATE;
Result10 rows · 4 columns
CUSTOMER_IDCUSTOMER_NAMECOUNTRYSIGNUP_DATE
101Northwind TradersUSA2022-01-15
102Blue Ridge SupplyUSA2022-04-02
103Kestrel AnalyticsCanada2022-06-21
104Harbour FoodsCanada2023-02-08
105Alpine LogisticsNULL2023-05-30
106Delta Print CoUSA2023-09-12
107Meridian HealthUSA2024-01-07
108Copper Kettle LtdUK2024-03-25
109Vantage MediaUK2024-08-14
110Sable InteriorsUSA2024-11-03

That is everything we need. Let's start taking a date apart.

Let's start with DATE_TRUNC

DATE_TRUNC takes a part and a date, and gives us back the start of the period that date falls in.

Let's ask for every useful part on the anchor at once.

Query
SELECT DATE '2024-06-30'                         AS ANCHOR,
       DATE_TRUNC('year',    DATE '2024-06-30')  AS YEAR_,
       DATE_TRUNC('quarter', DATE '2024-06-30')  AS QUARTER_,
       DATE_TRUNC('month',   DATE '2024-06-30')  AS MONTH_,
       DATE_TRUNC('week',    DATE '2024-06-30')  AS WEEK_,
       DATE_TRUNC('day',     DATE '2024-06-30')  AS DAY_;
Result1 row · 6 columns · scroll →
ANCHORYEAR_QUARTER_MONTH_WEEK_DAY_
2024-06-302024-01-012024-04-012024-06-012024-06-242024-06-30

Year gave us the 1st of January. Quarter gave us the 1st of April. Month gave us the 1st of June. Day gave us the date back untouched. And all of those answers are still dates in 2024, so nothing was turned into a number.

Now look at the week column. It landed on 2024-06-24, which is not the 1st of anything.

So what day does that week start on?

Let's ask directly rather than work it out. We want the weekday of the anchor, the start of its week, and the weekday of that.

Query
SELECT DATE '2024-06-30'                            AS ANCHOR,
       DAYNAME(DATE '2024-06-30')                   AS ANCHOR_DAY,
       DATE_TRUNC('week', DATE '2024-06-30')        AS WEEK_START,
       DAYNAME(DATE_TRUNC('week', DATE '2024-06-30')) AS WEEK_START_DAY,
       DATE_TRUNC('week', DATE '2024-07-01')        AS NEXT_MONDAYS_WEEK;
Result1 row · 5 columns
ANCHORANCHOR_DAYWEEK_STARTWEEK_START_DAYNEXT_MONDAYS_WEEK
2024-06-30Sun2024-06-24Mon2024-07-01

The anchor 2024-06-30 is a Sun, and DATE_TRUNC('week', ...) put it in the week beginning 2024-06-24, which is a Mon.

The last column confirms the rule from the other side. 2024-07-01 truncates to itself, so that date is the first day of its own week.

So DATE_TRUNC runs the week from Monday to Sunday. Hold on to that, because we are going to find something later that disagrees with it.

Now let's try the same function on a timestamp

On a TIMESTAMP the smaller parts become available.

Query
SELECT '2024-06-30 14:37:52.123'::TIMESTAMP_NTZ                          AS ANCHOR,
       DATE_TRUNC('day',    '2024-06-30 14:37:52.123'::TIMESTAMP_NTZ)    AS DAY_,
       DATE_TRUNC('hour',   '2024-06-30 14:37:52.123'::TIMESTAMP_NTZ)    AS HOUR_,
       DATE_TRUNC('minute', '2024-06-30 14:37:52.123'::TIMESTAMP_NTZ)    AS MINUTE_,
       DATE_TRUNC('second', '2024-06-30 14:37:52.123'::TIMESTAMP_NTZ)    AS SECOND_;
Result1 row · 5 columns
ANCHORDAY_HOUR_MINUTE_SECOND_
2024-06-30 14:37:52.1230002024-06-30 00:00:002024-06-30 14:00:002024-06-30 14:37:002024-06-30 14:37:52

Each one zeroed everything below it. DATE_TRUNC('day', ...) on a timestamp is the usual way to get a day's worth of rows out of a timestamp column, and notice that it gave us midnight on that day, still a timestamp, rather than a DATE.

Now let's try DATE_PART

DATE_PART does the other job. Give it a part and a date and it pulls one number out.

Let's ask for every part of the anchor.

Query
SELECT DATE '2024-06-30'                          AS ANCHOR,
       DATE_PART('year',       DATE '2024-06-30') AS YEAR_,
       DATE_PART('quarter',    DATE '2024-06-30') AS QUARTER_,
       DATE_PART('month',      DATE '2024-06-30') AS MONTH_,
       DATE_PART('day',        DATE '2024-06-30') AS DAY_,
       DATE_PART('dayofweek',  DATE '2024-06-30') AS DAY_OF_WEEK,
       DATE_PART('dayofyear',  DATE '2024-06-30') AS DAY_OF_YEAR;
Result1 row · 7 columns · scroll →
ANCHORYEAR_QUARTER_MONTH_DAY_DAY_OF_WEEKDAY_OF_YEAR
2024-06-30202426300182

Year 2024, quarter 2, month 6, day 30 and day-of-year 182. All numbers, exactly as advertised.

And day-of-week 0. Zero for which day? That one needs its own grid, and it gets one in the next section.

EXTRACT is the standard SQL spelling

EXTRACT(part FROM date) is the ANSI SQL form of the same operation. Let's run it beside DATE_PART and compare the two in the query itself rather than by eye.

Query
SELECT EXTRACT(year FROM DATE '2024-06-30')   AS YEAR_,
       EXTRACT(month FROM DATE '2024-06-30')  AS MONTH_,
       EXTRACT(day FROM DATE '2024-06-30')    AS DAY_,
       DATE_PART('year', DATE '2024-06-30') = EXTRACT(year FROM DATE '2024-06-30')
         AS SAME;
Result1 row · 4 columns
YEAR_MONTH_DAY_SAME
2024630TRUE

The last column is the comparison, and it came back TRUE. Use whichever you prefer. EXTRACT ports to other databases more cleanly, and DATE_PART reads more like the rest of Snowflake's date functions.

The same thing on the time parts

On a timestamp we can ask for the hour, the minute and the second too.

Query
SELECT '2024-06-30 14:37:52.123'::TIMESTAMP_NTZ                        AS ANCHOR,
       DATE_PART('hour',   '2024-06-30 14:37:52.123'::TIMESTAMP_NTZ)   AS HOUR_,
       DATE_PART('minute', '2024-06-30 14:37:52.123'::TIMESTAMP_NTZ)   AS MINUTE_,
       DATE_PART('second', '2024-06-30 14:37:52.123'::TIMESTAMP_NTZ)   AS SECOND_,
       DATE_PART('epoch_second', '2024-06-30 14:37:52'::TIMESTAMP_NTZ) AS EPOCH_SECOND;
Result1 row · 5 columns
ANCHORHOUR_MINUTE_SECOND_EPOCH_SECOND
2024-06-30 14:37:52.1230001437521719758272

Those three came straight out. 'epoch_second' is the odd one in that list. It is not a part of the timestamp at all. It is the whole timestamp expressed as seconds since the Unix epoch, which is what you want when something downstream expects an integer.

And there are parts below the second as well.

Query
SELECT DATE_PART('nanosecond', '2024-06-30 14:37:52.123'::TIMESTAMP_NTZ) AS NANOSECOND_,
       DATE_PART('epoch_millisecond', '2024-06-30 14:37:52.123'::TIMESTAMP_NTZ)
         AS EPOCH_MILLISECOND,
       DATE_TRUNC('second', '2024-06-30 14:37:52.123'::TIMESTAMP_NTZ)
         AS TRUNCATED_TO_THE_SECOND;
Result1 row · 3 columns
NANOSECOND_EPOCH_MILLISECONDTRUNCATED_TO_THE_SECOND
12300000017197582721232024-06-30 14:37:52

'nanosecond' gave us the fractional part scaled to nanoseconds, and 'epoch_millisecond' gave us the epoch value with that fraction folded in.

The third column is the contrast worth noticing. DATE_TRUNC('second', ...) kept the timestamp and threw the fraction away, rather than turning it into a number.

Zero for which day? Let's number a whole week

We left a question open in the last grid. Let's generate seven consecutive days from the anchor and put both of Snowflake's day numberings beside their names.

Query
SELECT D.THE_DATE,
       DAYNAME(D.THE_DATE)                     AS NAME_,
       DATE_PART('dayofweek', D.THE_DATE)      AS DOW,
       DATE_PART('dayofweekiso', D.THE_DATE)   AS DOW_ISO
FROM (SELECT DATEADD('day', SEQ4(), DATE '2024-06-30') AS THE_DATE
      FROM TABLE(GENERATOR(ROWCOUNT => 7))) D
ORDER BY D.THE_DATE;
Result7 rows · 4 columns
THE_DATENAME_DOWDOW_ISO
2024-06-30Sun07
2024-07-01Mon11
2024-07-02Tue22
2024-07-03Wed33
2024-07-04Thu44
2024-07-05Fri55
2024-07-06Sat66

'dayofweek' numbers Sun as 0 and runs up to Sat as 6. That is the 0 from the previous section explained.

'dayofweekiso' does it differently. It numbers Mon as 1 and Sun as 7.

Which leaves us with two statements that are both true in the same session at the same moment. The truncation functions treat the week as starting on Monday, and 'dayofweek' numbers it as if it started on Sunday.

Rather than guess at the history, let's ask the database.

Query
SHOW PARAMETERS LIKE 'WEEK_START' IN SESSION;
Result1 row · 6 columns · scroll →
keyvaluedefaultleveldescriptiontype
WEEK_START00Defines the first day of the week: 0: legacy Snowflake behavior; 1: Monday .. 7: Sunday.NUMBER

The parameter's own description is right there in the grid. The value is 0, and 0 is documented as legacy behaviour, which is exactly the split we just watched. Set it to a value between 1 and 7 and you pin the week to a real day, after which both halves agree with each other.

It is a session setting, not a constant

WEEK_START is a session parameter, so the same query can return different week boundaries for two different users. If a week definition matters to a report, set it explicitly in the session or do not depend on it. The ISO parts and 'dayofweekiso' do not move.

What happens at the end of a year?

There is one more week problem and it only shows up once a year. Let's take eight consecutive days across the end of 2024 and put every week-related part beside them.

Query
SELECT D.THE_DATE,
       DAYNAME(D.THE_DATE)                     AS NAME_,
       DATE_PART('weekofyear', D.THE_DATE)     AS WEEK_,
       DATE_PART('weekiso', D.THE_DATE)        AS WEEK_ISO,
       DATE_PART('yearofweek', D.THE_DATE)     AS YEAR_OF_WEEK,
       DATE_PART('yearofweekiso', D.THE_DATE)  AS YEAR_OF_WEEK_ISO
FROM (SELECT DATEADD('day', SEQ4(), DATE '2024-12-28') AS THE_DATE
      FROM TABLE(GENERATOR(ROWCOUNT => 8))) D
ORDER BY D.THE_DATE;
Result8 rows · 6 columns · scroll →
THE_DATENAME_WEEK_WEEK_ISOYEAR_OF_WEEKYEAR_OF_WEEK_ISO
2024-12-28Sat525220242024
2024-12-29Sun525220242024
2024-12-30Mon1120252025
2024-12-31Tue1120252025
2025-01-01Wed1120252025
2025-01-02Thu1120252025
2025-01-03Fri1120252025
2025-01-04Sat1120252025

2024-12-29 is in week 52. 2024-12-30 is in week 1. Still December, and already week 1, because the grid says that date is a Mon and a new week has started.

Now read the YEAR_OF_WEEK column on that same row. It says 2025. That week belongs to 2025 even though the date is in 2024, which is the whole reason 'yearofweek' exists.

The mistake this grid prevents

Label that week with DATE_PART('year', ...) and you get “week 1 of 2024”, which is a week that exists, sits at the beginning of 2024, and is not the week you are looking at. Two rows then collide in a report and the totals are quietly wrong. Pair 'weekofyear' with 'yearofweek', always, or 'weekiso' with 'yearofweekiso'.

Why both pairs are in the grid

On this data the ISO columns agree with the non-ISO ones row for row, because WEEK_START is at its default. They are not guaranteed to agree once that parameter is changed, which is the argument for picking the ISO pair and staying with it.

So what is the real difference between the two?

We have now used both functions on the same date. Let's put them in one row with their types, because this is the line the next section depends on.

Query
SELECT DATE_TRUNC('month', DATE '2024-06-30')                  AS TRUNC_SAYS,
       TYPEOF(DATE_TRUNC('month', DATE '2024-06-30')::VARIANT) AS TRUNC_TYPE,
       DATE_PART('month', DATE '2024-06-30')                   AS PART_SAYS,
       TYPEOF(DATE_PART('month', DATE '2024-06-30')::VARIANT)  AS PART_TYPE;
Result1 row · 4 columns
TRUNC_SAYSTRUNC_TYPEPART_SAYSPART_TYPE
2024-06-01DATE6INTEGER
DATE_TRUNC('month', d)DATE_PART('month', d)
Returns2024-06-016
TypeDATEINTEGER
Keeps the year?YesNo, it is gone
Good forGrouping, joining to a calendar, sortingLabelling, filtering on a single part, maths

One of them keeps the year. The other throws it away.

Both are correct functions doing exactly what they say. The problem is that a month number looks like a month.

Now let's group some orders by month

Let's write the report. Orders per month, grouped with DATE_TRUNC.

Query
SELECT DATE_TRUNC('month', ORDER_DATE) AS MONTH_,
       COUNT(*) AS ORDERS_, ROUND(SUM(AMOUNT), 2) AS TOTAL
FROM ORDERS
GROUP BY DATE_TRUNC('month', ORDER_DATE)
ORDER BY MONTH_;
Result23 rows · 3 columns
MONTH_ORDERS_TOTAL
2023-01-0125090.50
2023-02-0127650.00
2023-03-0127330.75
2023-04-0129010.00
2023-05-0123980.25
2023-06-01210075.50
2023-07-0127170.00
2023-08-01212110.00
2023-09-0115540.00
2023-10-0128445.00
2023-11-01211380.00
2023-12-0128980.00
2024-01-01215110.00
2024-02-0118340.00
2024-03-0122930.00
2024-04-01212640.00
2024-05-01216875.00
2024-06-01211660.00
2024-07-0118820.00
2024-08-01215640.00
2024-09-0127010.00
2024-10-0119640.00
2024-11-011NULL

23 rows, one per month that actually has orders in it, running from January 2023 to November 2024. That is what the data is.

Have a look at the bottom row while it is on screen, because it is the only thing in this section that is not about date functions. November 2024 has one order and a NULL total. The group has a single row, that row's amount is NULL, and SUM of nothing but NULL is NULL. That is the behaviour from Episode 71 turning up in a real report.

Now let's write the same report with DATE_PART instead. This is the version people actually write, because the output column is a tidy little month number.

Query
SELECT DATE_PART('month', ORDER_DATE) AS MONTH_NUMBER,
       COUNT(*) AS ORDERS_, ROUND(SUM(AMOUNT), 2) AS TOTAL
FROM ORDERS
GROUP BY DATE_PART('month', ORDER_DATE)
ORDER BY MONTH_NUMBER;
Result12 rows · 3 columns
MONTH_NUMBERORDERS_TOTAL
1420200.50
2315990.00
3410260.75
4421650.00
5420855.25
6421735.50
7315990.00
8427750.00
9312550.00
10318085.00
11311380.00
1228980.00

12 rows, and every one of them looks reasonable. There is a month number, an order count and a total. Nothing is NULL any more. No error, no warning, no hint.

And there is no year anywhere in that grid. January 2023 and January 2024 have been added together and labelled 1. Add up the two January rows of the previous grid and you land exactly on that row.

The previous grid is the proof, because it has two separate January rows in it. But rather than leave that as something to compare by eye, let's count it.

Query
SELECT COUNT(DISTINCT DATE_TRUNC('month', ORDER_DATE))  AS REAL_MONTHS,
       COUNT(DISTINCT DATE_PART('month', ORDER_DATE))   AS MONTH_NUMBERS,
       COUNT(DISTINCT DATE_PART('year', ORDER_DATE))    AS YEARS_PRESENT
FROM ORDERS;
Result1 row · 3 columns
REAL_MONTHSMONTH_NUMBERSYEARS_PRESENT
23122

23 real months, 12 month numbers, 2 years present. 23 months arrived as 12 rows and nothing said so.

The NULL total went missing too. It was absorbed into a group that had other rows in it, so the sum of that group is a number again.

The fix is one column

If you group by a part, put the year in the GROUP BY as well, or group by DATE_TRUNC and format the label afterwards. This is the failure mode that produces a chart with impossible seasonality in it, and it survives review because the SQL is correct. Nothing is wrong with DATE_PART. It answered the question it was asked.

A check worth one query

Here is a quick way to catch it on any report. Count the distinct values of your grouping expression and compare that with the number of rows you got back. If grouping collapsed something you did not intend, the two numbers disagree.

Let's try LAST_DAY

LAST_DAY is the other end of DATE_TRUNC. Give it a date and get the last day of its month.

Query
SELECT LAST_DAY(DATE '2024-06-15')             AS END_OF_JUNE,
       LAST_DAY(DATE '2024-02-15')             AS END_OF_FEB_LEAP,
       LAST_DAY(DATE '2023-02-15')             AS END_OF_FEB_NON_LEAP,
       LAST_DAY(DATE '2024-06-30')             AS ALREADY_THE_LAST_DAY;
Result1 row · 4 columns
END_OF_JUNEEND_OF_FEB_LEAPEND_OF_FEB_NON_LEAPALREADY_THE_LAST_DAY
2024-06-302024-02-292023-02-282024-06-30

June gave us the 30th. February 2024 gave us the 29th and February 2023 gave us the 28th, so it knows about leap years without being told.

And handed a date that is already the last day of its month, it gave that date straight back. The function is idempotent, which is what you want in a pipeline that might run twice.

It takes a second argument too

Let's try all four parts it accepts.

Query
SELECT LAST_DAY(DATE '2024-06-15', 'month')   AS MONTH_,
       LAST_DAY(DATE '2024-06-15', 'quarter') AS QUARTER_,
       LAST_DAY(DATE '2024-06-15', 'year')    AS YEAR_,
       LAST_DAY(DATE '2024-06-15', 'week')    AS WEEK_;
Result1 row · 4 columns
MONTH_QUARTER_YEAR_WEEK_
2024-06-302024-06-302024-12-312024-06-16

On a June date the month and quarter answers are the same date, because June is the last month of its quarter. The 'week' answer is 2024-06-16, a date in the middle of the month, because weeks do not line up with months and LAST_DAY is not pretending otherwise.

Now let's write the pattern you will actually use

First and last day of the month for every order, plus the number of days in that month worked out from the two.

Query
SELECT ORDER_ID, ORDER_DATE,
       DATE_TRUNC('month', ORDER_DATE) AS MONTH_START,
       LAST_DAY(ORDER_DATE)            AS MONTH_END,
       DATEDIFF('day', DATE_TRUNC('month', ORDER_DATE), LAST_DAY(ORDER_DATE)) + 1
         AS DAYS_IN_MONTH
FROM ORDERS
ORDER BY ORDER_ID
LIMIT 10;
Result10 rows · 5 columns
ORDER_IDORDER_DATEMONTH_STARTMONTH_ENDDAYS_IN_MONTH
10012023-01-122023-01-012023-01-3131
10022023-01-282023-01-012023-01-3131
10032023-02-142023-02-012023-02-2828
10042023-02-272023-02-012023-02-2828
10052023-03-092023-03-012023-03-3131
10062023-03-222023-03-012023-03-3131
10072023-04-052023-04-012023-04-3030
10082023-04-192023-04-012023-04-3030
10092023-05-022023-05-012023-05-3131
10102023-05-212023-05-012023-05-3131

DATE_TRUNC for the start, LAST_DAY for the end, and DATEDIFF between them plus one for the length. The plus one is there because DATEDIFF counts the gaps between the days and not the days themselves.

The DAYS_IN_MONTH column is correct on every row, February included. But that is worth checking rather than eyeballing, so let's run it over the whole table.

Query
SELECT COUNT(*) AS ROWS_TOTAL,
       MIN(DATEDIFF('day', DATE_TRUNC('month', ORDER_DATE), LAST_DAY(ORDER_DATE)) + 1)
         AS SHORTEST_MONTH,
       MAX(DATEDIFF('day', DATE_TRUNC('month', ORDER_DATE), LAST_DAY(ORDER_DATE)) + 1)
         AS LONGEST_MONTH,
       COUNT_IF(DATEDIFF('day', DATE_TRUNC('month', ORDER_DATE), LAST_DAY(ORDER_DATE)) + 1
                NOT BETWEEN 28 AND 31) AS OUT_OF_RANGE
FROM ORDERS;
Result1 row · 4 columns
ROWS_TOTALSHORTEST_MONTHLONGEST_MONTHOUT_OF_RANGE
4128310

41 rows, shortest month 28, longest 31, and 0 rows outside that range. The expression holds everywhere in this data, which is a better thing to know than that it held on the ten rows above.

Now let's try NEXT_DAY and PREVIOUS_DAY

NEXT_DAY takes a date and a day name and gives you the next occurrence of that weekday.

Query
SELECT DATE '2024-06-30'                            AS ANCHOR,
       DAYNAME(DATE '2024-06-30')                   AS ANCHOR_DAY,
       NEXT_DAY(DATE '2024-06-30', 'Monday')        AS NEXT_MONDAY,
       NEXT_DAY(DATE '2024-06-30', 'Friday')        AS NEXT_FRIDAY,
       PREVIOUS_DAY(DATE '2024-06-30', 'Monday')    AS PREVIOUS_MONDAY;
Result1 row · 5 columns
ANCHORANCHOR_DAYNEXT_MONDAYNEXT_FRIDAYPREVIOUS_MONDAY
2024-06-30Sun2024-07-012024-07-052024-06-24

From the anchor, which this grid again confirms is a Sun, the next Monday is 2024-07-01 and the next Friday is 2024-07-05. PREVIOUS_DAY goes the other way, to 2024-06-24.

What happens when we are already on that weekday?

This is the edge that catches people, and it is worth knowing before it costs you a scheduling bug. Let's start from 2024-07-01, which the previous grid showed is the Monday in question, and ask for the next Monday.

Query
SELECT DATE '2024-07-01'                          AS ANCHOR,
       DAYNAME(DATE '2024-07-01')                 AS ANCHOR_DAY,
       NEXT_DAY(DATE '2024-07-01', 'Monday')      AS NEXT_MONDAY,
       PREVIOUS_DAY(DATE '2024-07-01', 'Monday')  AS PREVIOUS_MONDAY,
       DATEDIFF('day', DATE '2024-07-01', NEXT_DAY(DATE '2024-07-01', 'Monday'))
         AS DAYS_FORWARD;
Result1 row · 5 columns
ANCHORANCHOR_DAYNEXT_MONDAYPREVIOUS_MONDAYDAYS_FORWARD
2024-07-01Mon2024-07-082024-06-247

We got 2024-07-08, and the last column measures the jump: 7 days. PREVIOUS_DAY skipped straight back past the date as well, to the Monday before.

So NEXT_DAY means strictly next and PREVIOUS_DAY means strictly previous. Neither one ever gives you back the date you handed it, which means “the Monday of this date's week” is not what either function computes. Episode 74 builds that one out of the ISO parts instead.

How fussy is the day-name argument?

Let's try four spellings of the same day.

Query
SELECT NEXT_DAY(DATE '2024-06-30', 'mo')        AS MO,
       NEXT_DAY(DATE '2024-06-30', 'mon')       AS MON,
       NEXT_DAY(DATE '2024-06-30', 'monday')    AS MONDAY_LOWER,
       NEXT_DAY(DATE '2024-06-30', 'MONDAY')    AS MONDAY_UPPER;
Result1 row · 4 columns
MOMONMONDAY_LOWERMONDAY_UPPER
2024-07-012024-07-012024-07-012024-07-01

'mo', 'mon', 'monday' and 'MONDAY' all gave the same answer. Case does not matter and two letters is enough.

That is handy, and it is also a reason to write the full name anyway. 'mo' is not obviously a day to the next person reading your query.

One last thing: one row of a date dimension

Let's put everything on this page into one query over real orders. The month each one belongs to, the end of that month, its quarter, its day name, its position in the year, and the Monday that follows it.

Query
SELECT ORDER_ID, ORDER_DATE,
       DATE_TRUNC('month', ORDER_DATE)      AS MONTH_START,
       LAST_DAY(ORDER_DATE)                 AS MONTH_END,
       DATE_PART('quarter', ORDER_DATE)     AS QUARTER_,
       DAYNAME(ORDER_DATE)                  AS DAY_NAME,
       DATE_PART('dayofyear', ORDER_DATE)   AS DAY_OF_YEAR,
       NEXT_DAY(ORDER_DATE, 'Monday')       AS NEXT_MONDAY
FROM ORDERS
ORDER BY ORDER_ID
LIMIT 5;
Result5 rows · 8 columns · scroll →
ORDER_IDORDER_DATEMONTH_STARTMONTH_ENDQUARTER_DAY_NAMEDAY_OF_YEARNEXT_MONDAY
10012023-01-122023-01-012023-01-311Thu122023-01-16
10022023-01-282023-01-012023-01-311Sat282023-01-30
10032023-02-142023-02-012023-02-281Tue452023-02-20
10042023-02-272023-02-012023-02-281Mon582023-03-06
10052023-03-092023-03-012023-03-311Thu682023-03-13

That is a row of a date dimension, and this is genuinely how one gets built. A generated series of dates with a column per part, materialised once, so that reports join to it instead of recomputing all of this on every query.

Every column in that grid comes from a function we have already run on this page, which is the point. Nothing new was needed to build it.

One of those columns does have more to it than we have used here. DAYNAME is the function behind the weekday names we have been reading off every grid in this post, and it, its month equivalent, and what they will and will not give you are the whole of the next post.

Key points

  • DATE_TRUNC(part, date) returns the start of the period as a DATE. DATE_PART(part, date) returns one number as an INTEGER.
  • Group by the truncation, not the part. Grouping these orders by DATE_PART('month', ...) turned 23 real months into 12 rows and merged two years together without an error or a warning.
  • A quick guard against that: compare the distinct count of your grouping expression with the number of rows the report returned.
  • EXTRACT(part FROM date) is the standard SQL spelling of DATE_PART, and the captured comparison column says the two agree.
  • 'dayofweek' numbers Sun as 0. 'dayofweekiso' numbers Mon as 1 and Sun as 7.
  • Meanwhile DATE_TRUNC('week', ...) starts the week on Monday. Both are true at once because WEEK_START is 0, which the parameter's own description calls legacy behaviour. And it is a session setting, so it can differ between users.
  • At a year boundary, 'weekofyear' alone is not enough. 2024-12-30 is week 1 of 'yearofweek' 2025. Pair the week part with the year-of-week part, always.
  • LAST_DAY knows about leap years, leaves an existing month-end unchanged, and takes an optional part: 'month', 'quarter', 'year' or 'week'.
  • DATE_TRUNC for the month start, LAST_DAY for the month end, and DATEDIFF plus one between them gives you the days in the month. Checked here across all 41 orders, with nothing outside 28 to 31.
  • NEXT_DAY and PREVIOUS_DAY are strict. From a Monday, the next Monday is 7 days away. Day names are case-insensitive and two letters is enough.
Next in this series

Episode 74 — day names, month names and buckets. DAYNAME and MONTHNAME, and why there is no full-name function. A weekday-versus-weekend test and how to check its magic numbers. And TIME_SLICE, which makes buckets that are not calendar periods at all.

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. 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, and every weekday named in the text above is taken from a DAYNAME column in one of the grids rather than worked out here. No performance or benchmark claims are made anywhere in this series. Function behaviour and the WEEK_START description follow 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)