Snowflake SQL Tutorial 75 - TO_CHAR, TO_DATE, TRY_TO_DATE - Dates 4

Snowflake SQL · Episode 75

In this post we are going to cover TO_CHAR, TO_DATE and TRY_TO_DATE in Snowflake.

These three move between text and dates. TO_CHAR takes a date and gives you back text in whatever shape you ask for. TO_DATE goes the other way: you hand it text and the format that text is in, and you get a real date. TRY_TO_DATE is the same thing, except that when the text does not fit it hands you a NULL instead of stopping the query.

We will start with one date and six format strings, and build it up step by step. On the way we will meet a one-character mistake that prints a perfectly plausible time and is wrong on every row, and a date format that can be read two ways without anything telling you which one you got.

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

Episode 75 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 TO_CHAR on a date
  3. Now let's format a timestamp, and meet the quiet one
  4. Now let's do the twelve-hour clock
  5. Careful: TO_CHAR on a number is a different format language
  6. Now let's format some real data
  7. Now let's go the other way with TO_DATE
  8. What happens when the text does not match the format?
  9. Let's use TRY_TO_DATE instead
  10. Now the dangerous one: a date that can be read two ways
  11. How bad is that on our own rows?
  12. Can we just let Snowflake work the format out?
  13. Now let's convert the whole column for real
  14. Why bother? Because sorting text is not sorting dates
  15. Now let's parse a timestamp, and an epoch
  16. One last thing: does a round trip survive?

Let's create some sample data

This post works on two tables. SALES_RAW is the deliberately messy one from Level 5, where every column is text. ORDERS is the clean table the whole course uses, and we will come to it near the end.

Copy the script below into a Snowflake worksheet and run it once.

Sample data
-- Sample data for this post.
-- Run this once. SALES_RAW is deliberately dirty: every column is text, and
-- the date column holds three different date formats on purpose.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE SALES_RAW (
    ROW_ID        INT,
    CUSTOMER_TXT  VARCHAR,
    AMOUNT_TXT    VARCHAR,
    DATE_TXT      VARCHAR,
    REGION_TXT    VARCHAR,
    PHONE_TXT     VARCHAR
);

INSERT INTO SALES_RAW VALUES
    (1, '  Northwind Traders ', '$4,200.00', '2023-01-12',  'us-east',    '(704) 555-0142'),
    (2, 'blue ridge supply',    '890.50',    '01/28/2023',  'US-EAST',    '704-555-0198'),
    (3, 'KESTREL ANALYTICS',    '$6,150',    '14-Feb-2023', 'ca-west',    '+1 416 555 0117'),
    (4, 'Harbour Foods  ',      '2,310.75',  '2023-03-09',  'CA-West',    '604.555.0163'),
    (5, 'Alpine Logistics',     'N/A',       '2023-04-05',  'eu-central', '+41 44 555 0188'),
    (6, ' Delta Print Co',      '$640.00',   '05/21/2023',  'us-east',    '919-555-0124'),
    (7, 'Meridian Health',      '9,250.00',  '14-Aug-2023', 'US-East',    '(617) 555-0179'),
    (8, 'copper kettle ltd',    '$1,080',    '2024-09-29',  'uk-north',   '+44 161 555 0133');

-- The clean ORDERS table further down this page is the one the whole course
-- uses. You already have it if you have been following along.

Now let's look at the messy table.

Query
SELECT ROW_ID, CUSTOMER_TXT, AMOUNT_TXT, DATE_TXT, REGION_TXT
FROM SALES_RAW ORDER BY ROW_ID;
Result8 rows · 5 columns
ROW_IDCUSTOMER_TXTAMOUNT_TXTDATE_TXTREGION_TXT
1 Northwind Traders $4,200.002023-01-12us-east
2blue ridge supply890.5001/28/2023US-EAST
3KESTREL ANALYTICS$6,15014-Feb-2023ca-west
4Harbour Foods 2,310.752023-03-09CA-West
5Alpine LogisticsN/A2023-04-05eu-central
6 Delta Print Co$640.0005/21/2023us-east
7Meridian Health9,250.0014-Aug-2023US-East
8copper kettle ltd$1,0802024-09-29uk-north

Eight rows and five columns. The column we care about is DATE_TXT, and it is worth looking at slowly.

Row 1 says 2023-01-12. Row 2 says 01/28/2023. Row 3 says 14-Feb-2023. Three different date shapes, in one column, in a column that is text.

Let's count them properly instead of eyeballing them. One regular expression per shape.

Query
SELECT CASE WHEN REGEXP_LIKE(DATE_TXT, '[0-9]{4}-[0-9]{2}-[0-9]{2}') THEN 'ISO yyyy-mm-dd'
            WHEN REGEXP_LIKE(DATE_TXT, '[0-9]{2}/[0-9]{2}/[0-9]{4}') THEN 'US mm/dd/yyyy'
            WHEN REGEXP_LIKE(DATE_TXT, '[0-9]{2}-[A-Za-z]{3}-[0-9]{4}') THEN 'dd-Mon-yyyy'
            ELSE 'unrecognised' END AS FORMAT,
       COUNT(*) AS N,
       MIN(DATE_TXT) AS AN_EXAMPLE
FROM SALES_RAW
GROUP BY 1
ORDER BY N DESC;
Result3 rows · 3 columns
FORMATNAN_EXAMPLE
ISO yyyy-mm-dd42023-01-12
US mm/dd/yyyy201/28/2023
dd-Mon-yyyy214-Aug-2023

Four rows are ISO, two use slashes and two use a month name. That adds up to eight, and the ELSE 'unrecognised' branch never fired, so there is no fourth shape hiding in there.

Have a look at the AN_EXAMPLE column. It is a MIN() over the text, so the month-name row shows 14-Aug-2023 rather than 14-Feb-2023. As text, A sorts before F.

Let's start with TO_CHAR on a date

TO_CHAR takes two things: a date, and a format string that says what the text should look like.

Let's take one date and ask for it six different ways.

Query
SELECT TO_CHAR(DATE '2024-06-30', 'YYYY-MM-DD')   AS ISO,
       TO_CHAR(DATE '2024-06-30', 'DD/MM/YYYY')   AS UK,
       TO_CHAR(DATE '2024-06-30', 'MM/DD/YYYY')   AS US,
       TO_CHAR(DATE '2024-06-30', 'DD-MON-YYYY')  AS DD_MON_YYYY,
       TO_CHAR(DATE '2024-06-30', 'DY')           AS DAY_ABBREV,
       TO_CHAR(DATE '2024-06-30', 'MON')          AS MONTH_ABBREV;
Result1 row · 6 columns · scroll →
ISOUKUSDD_MON_YYYYDAY_ABBREVMONTH_ABBREV
2024-06-3030/06/202406/30/202430-Jun-2024SunJun

One row, six columns, same date in all of them. YYYY-MM-DD gives the ISO form. DD/MM/YYYY puts the day first and MM/DD/YYYY puts the month first. DD-MON-YYYY spells the month out short.

The last two are the short ones. DY gives the day of the week and MON gives the month, both abbreviated to three letters.

Those are the building blocks. Everything else in this post is made out of them.

Now let's format a timestamp, and meet the quiet one

Dates have no time in them, so for the next bit we need a timestamp. This is where the one mistake I want you to remember lives.

Let's take one timestamp and format the time part two ways, side by side. One uses MM in the middle position and one uses MI.

Query
SELECT '2024-06-30 14:37:52'::TIMESTAMP_NTZ                            AS ANCHOR,
       TO_CHAR('2024-06-30 14:37:52'::TIMESTAMP_NTZ, 'HH24:MM:SS')     AS WRONG_MM,
       TO_CHAR('2024-06-30 14:37:52'::TIMESTAMP_NTZ, 'HH24:MI:SS')     AS RIGHT_MI,
       TO_CHAR('2024-06-30 14:37:52'::TIMESTAMP_NTZ, 'YYYY-MM-DD HH24:MI:SS') AS FULL_;
Result1 row · 4 columns
ANCHORWRONG_MMRIGHT_MIFULL_
2024-06-30 14:37:5214:06:5214:37:522024-06-30 14:37:52

Read those two columns against the anchor. The anchor is 14:37:52. The MI version gives 14:37:52, which is right. The MM version gives 14:06:52.

MM is the month. The anchor is in June, so the month is 06, and 06 went straight into the minutes position.

This is the one to remember

There was no error and no warning. 14:06:52 is a perfectly ordinary time of day. Nothing on the screen tells you it is the month. In a time format, MM is the month and MI is the minutes, and that one character is the difference between a right answer and a wrong one on every single row.

The last column shows the whole thing written out with MM in the date position and MI in the time position, which is where each of them belongs.

Now let's do the twelve-hour clock

HH24 gives the twenty-four hour clock. For the twelve-hour clock we want HH12, and we need AM in the format as well so we can tell morning from afternoon.

Let's run three different times through it, including one just after midnight.

Query
SELECT TO_CHAR('2024-06-30 14:37:52'::TIMESTAMP_NTZ, 'HH24:MI')    AS H24,
       TO_CHAR('2024-06-30 14:37:52'::TIMESTAMP_NTZ, 'HH12:MI AM') AS H12,
       TO_CHAR('2024-06-30 09:05:00'::TIMESTAMP_NTZ, 'HH12:MI AM') AS MORNING,
       TO_CHAR('2024-06-30 00:30:00'::TIMESTAMP_NTZ, 'HH12:MI AM') AS AFTER_MIDNIGHT;
Result1 row · 4 columns
H24H12MORNINGAFTER_MIDNIGHT
14:3702:37 PM09:05 AM12:30 AM

The afternoon time comes out as 02:37 PM and the morning one as 09:05 AM. Both read the way you would say them.

The last column is the one worth knowing. Half past midnight comes back as 12:30 AM, not 00:30. On a twelve-hour clock the hour after midnight is twelve, and AM is what tells you which twelve it is.

Careful: TO_CHAR on a number is a different format language

TO_CHAR also works on numbers, and the format string means something completely different there. This catches people who have just learned the date elements.

Let's put one number through three number masks, and put a date format beside them for comparison.

Query
SELECT TO_CHAR(1234.5678, '9999.99')      AS NUMBER_FORMAT,
       TO_CHAR(1234.5678, '0000.0000')    AS ZERO_PADDED,
       TO_CHAR(1234.5678, '9,999.99')     AS WITH_SEPARATOR,
       TO_CHAR(DATE '2024-06-30', 'YYYY') AS DATE_FORMAT_FOR_COMPARISON;
Result1 row · 4 columns
NUMBER_FORMATZERO_PADDEDWITH_SEPARATORDATE_FORMAT_FOR_COMPARISON
1234.57 1234.5678 1,234.572024

9999.99 gave us 1234.57, rounded to two decimal places. 0000.0000 gave us 1234.5678, which keeps four. So the thing that changed between those two columns is how many decimals the mask asked for.

Nothing got padded in this grid, and it is worth saying so. 1234 already fills the four digits in front of the point, so the zeroes in 0000.0000 had nothing left to do. You would need a shorter number to see padding happen.

The third mask, 9,999.99, puts the thousands separator in. And the last column is a reminder: YYYY on a date gives the year, while in a number mask the only characters that mean anything are the digit characters and the punctuation.

Two models, one function name

Do not carry your date format knowledge across to numbers. MM, DD and YYYY mean nothing in a number mask, and 9 and 0 mean nothing in a date mask. They are two separate format models that happen to share a function name.

Now let's format some real data

That is enough on format strings in the abstract. Let's put them on the ORDERS table and make a column you could actually put in a report.

Query
SELECT ORDER_ID, AMOUNT,
       TO_CHAR(AMOUNT, '9,999.00')  AS WITH_COMMAS,
       TO_VARCHAR(AMOUNT)           AS PLAIN,
       TO_CHAR(ORDER_DATE, 'DY DD MON YYYY') AS READABLE_DATE
FROM ORDERS
ORDER BY ORDER_ID
LIMIT 10;
Result10 rows · 5 columns
ORDER_IDAMOUNTWITH_COMMASPLAINREADABLE_DATE
10014200.00 4,200.004200.00Thu 12 Jan 2023
1002890.50 890.50890.50Sat 28 Jan 2023
10036150.00 6,150.006150.00Tue 14 Feb 2023
10041500.00 1,500.001500.00Mon 27 Feb 2023
10052310.75 2,310.752310.75Thu 09 Mar 2023
10065020.00 5,020.005020.00Wed 22 Mar 2023
10077890.00 7,890.007890.00Wed 05 Apr 2023
10081120.00 1,120.001120.00Wed 19 Apr 2023
10093340.25 3,340.253340.25Tue 02 May 2023
1010640.00 640.00640.00Sun 21 May 2023

Ten rows. WITH_COMMAS is the amount through a number mask and PLAIN is TO_VARCHAR with no mask at all, so you can see what the mask is buying you.

Look at the 890.50 row. The mask has a comma in it, but the number does not need one, so no comma appears. A separator in the mask is permission, not an instruction.

The last column is the date through DY DD MON YYYY. That is a report column. Nobody has to think about it to read it.

Now let's go the other way with TO_DATE

TO_DATE is the reverse. We give it text and the format that text is in, and it gives us a real date back.

Let's start with the easy ones. The ISO rows, read with the ISO format.

Query
SELECT ROW_ID, DATE_TXT,
       TO_DATE(DATE_TXT, 'YYYY-MM-DD') AS PARSED
FROM SALES_RAW
WHERE REGEXP_LIKE(DATE_TXT, '[0-9]{4}-[0-9]{2}-[0-9]{2}')
ORDER BY ROW_ID;
Result4 rows · 3 columns
ROW_IDDATE_TXTPARSED
12023-01-122023-01-12
42023-03-092023-03-09
52023-04-052023-04-05
82024-09-292024-09-29

Four rows, and all four parsed. The PARSED column looks the same as the text column here, but it is not text any more. It is a DATE.

Now the month-name rows, with the format they are actually in.

Query
SELECT ROW_ID, DATE_TXT,
       TO_DATE(DATE_TXT, 'DD-MON-YYYY') AS PARSED
FROM SALES_RAW
WHERE REGEXP_LIKE(DATE_TXT, '[0-9]{2}-[A-Za-z]{3}-[0-9]{4}')
ORDER BY ROW_ID;
Result2 rows · 3 columns
ROW_IDDATE_TXTPARSED
314-Feb-20232023-02-14
714-Aug-20232023-08-14

Two rows, and both parsed. 14-Feb-2023 came back as 2023-02-14. MON reads the three-letter month on the way in just as it writes it on the way out.

So when the format matches the text, this is a dull function. Good. Now let's break it.

What happens when the text does not match the format?

Row 5 of our table has N/A in the amount column, and plenty of real tables have that sort of thing in a date column too. Let's hand TO_DATE something that is not a date at all.

Query
SELECT TO_DATE('N/A', 'YYYY-MM-DD') AS SHOULD_NOT_GET_THIS_FAR;
Query failed — Snowflake returned an error
100097 (22007): Can't parse 'N/A' as date with format 'YYYY-MM-DD'

That is a good error. It names the value it could not read and it names the format it was trying to read it with. You do not have to guess at anything.

Now let's try real date text, in the wrong format. The text is a genuine date and the format is a genuine format. They just do not match each other.

Query
SELECT TO_DATE('01/28/2023', 'YYYY-MM-DD') AS SHOULD_NOT_GET_THIS_FAR;
Query failed — Snowflake returned an error
100097 (22007): Can't parse '01/28/2023' as date with format 'YYYY-MM-DD'

Same shape of error, same useful detail. And here is the third one, which is the same text read day-first instead.

Query
SELECT TO_DATE('01/28/2023', 'DD/MM/YYYY') AS SHOULD_NOT_GET_THIS_FAR;
Query failed — Snowflake returned an error
100097 (22007): Can't parse '01/28/2023' as date with format 'DD/MM/YYYY'

Read day-first, 01/28/2023 asks for month 28, and there is no such month, so Snowflake refuses. Hold on to this one. It is going to matter in a few sections.

One bad row stops everything

All three of those stopped the whole query. One bad value in one row and you get no rows back at all, not even the good ones. On a load, that is almost never what you want.

Let's use TRY_TO_DATE instead

TRY_TO_DATE takes the same arguments as TO_DATE. The difference is what it does when the text does not fit. Instead of throwing, it gives you NULL.

Let's put all four cases through it in one query: rubbish, the wrong format, text that is correct, and a NULL going in.

Query
SELECT TRY_TO_DATE('N/A', 'YYYY-MM-DD')             AS RUBBISH,
       TRY_TO_DATE('01/28/2023', 'YYYY-MM-DD')      AS WRONG_FORMAT,
       TRY_TO_DATE('2023-01-12', 'YYYY-MM-DD')      AS CORRECT,
       TRY_TO_DATE(NULL::VARCHAR, 'YYYY-MM-DD')     AS NULL_INPUT;
Result1 row · 4 columns
RUBBISHWRONG_FORMATCORRECTNULL_INPUT
NULLNULL2023-01-12NULL

Rubbish gives NULL. The wrong format gives NULL. The correct text gives a real date. And NULL in gives NULL out.

The query finished, and we can see which value was the problem instead of losing the whole result to it.

And it checks the calendar, not just the shape

This is the part people do not expect. TRY_TO_DATE is not pattern matching. It knows what a date is.

Let's give it four dates that look perfectly well formed and see which ones it accepts.

Query
SELECT TRY_TO_DATE('2023-02-30', 'YYYY-MM-DD') AS FEB_30,
       TRY_TO_DATE('2023-02-29', 'YYYY-MM-DD') AS FEB_29_NON_LEAP,
       TRY_TO_DATE('2024-02-29', 'YYYY-MM-DD') AS FEB_29_LEAP,
       TRY_TO_DATE('2023-13-01', 'YYYY-MM-DD') AS MONTH_13;
Result1 row · 4 columns
FEB_30FEB_29_NON_LEAPFEB_29_LEAPMONTH_13
NULLNULL2024-02-29NULL

The 30th of February is NULL. The 29th of February 2023 is NULL, because 2023 is not a leap year. The 29th of February 2024 comes back as a real date, because that year has one. And month 13 is NULL.

All four of those match YYYY-MM-DD perfectly. They were rejected on the calendar, not on the shape.

Now the dangerous one: a date that can be read two ways

Everything so far has either worked or told us it did not. This section is about the case where it does neither.

Read this text: 01/02/2023. Is that the 2nd of January, or the 1st of February?

It is both. Let's read it both ways in one query and compare the answers.

Query
SELECT '01/02/2023'                           AS THE_TEXT,
       TO_DATE('01/02/2023', 'MM/DD/YYYY')    AS READ_AS_US,
       TO_DATE('01/02/2023', 'DD/MM/YYYY')    AS READ_AS_UK,
       TO_DATE('01/02/2023', 'MM/DD/YYYY') = TO_DATE('01/02/2023', 'DD/MM/YYYY')
         AS SAME_DAY;
Result1 row · 4 columns
THE_TEXTREAD_AS_USREAD_AS_UKSAME_DAY
01/02/20232023-01-022023-02-01FALSE

Month-first gives 2023-01-02. Day-first gives 2023-02-01. The comparison column says FALSE.

Two different days, out of one piece of text, and both queries succeeded. Nothing failed, so nothing told us.

Now let's put that next to the one that does fail, so the difference is visible in a single grid.

Query
SELECT TRY_TO_DATE('01/28/2023', 'MM/DD/YYYY') AS UNAMBIGUOUS_US,
       TRY_TO_DATE('01/28/2023', 'DD/MM/YYYY') AS UNAMBIGUOUS_UK_IS_NULL,
       TRY_TO_DATE('01/02/2023', 'MM/DD/YYYY') AS AMBIGUOUS_US,
       TRY_TO_DATE('01/02/2023', 'DD/MM/YYYY') AS AMBIGUOUS_UK,
       TRY_TO_DATE('01/02/2023', 'MM/DD/YYYY')
         = TRY_TO_DATE('01/02/2023', 'DD/MM/YYYY') AS SAME_DAY;
Result1 row · 5 columns
UNAMBIGUOUS_USUNAMBIGUOUS_UK_IS_NULLAMBIGUOUS_USAMBIGUOUS_UKSAME_DAY
2023-01-28NULL2023-01-022023-02-01FALSE

01/28/2023 read month-first is the 28th of January. Read day-first it is NULL, because there is no 28th month. That one fails loudly.

01/02/2023 parses both ways and quietly hands you a different day, and the SAME_DAY column is FALSE.

The rule in one line

A day above 12 protects you, because the wrong reading asks for a month that does not exist and Snowflake refuses it. A day of 12 or under does not protect you at all. It parses either way and gives you another date.

How bad is that on our own rows?

Let's not leave that as a scary story. We have two slash-format rows in the table. Let's read them both ways and look at what happens.

Query
SELECT ROW_ID, DATE_TXT,
       TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY') AS PARSED_AS_US,
       TRY_TO_DATE(DATE_TXT, 'DD/MM/YYYY') AS PARSED_AS_UK
FROM SALES_RAW
WHERE REGEXP_LIKE(DATE_TXT, '[0-9]{2}/[0-9]{2}/[0-9]{4}')
ORDER BY ROW_ID;
Result2 rows · 4 columns
ROW_IDDATE_TXTPARSED_AS_USPARSED_AS_UK
201/28/20232023-01-28NULL
605/21/20232023-05-21NULL

Both of them parse month-first. Neither of them parses day-first, and both day-first columns are NULL.

So let's put a count on it rather than reading two rows and calling it done.

Query
SELECT COUNT(*) AS SLASH_FORMAT_ROWS,
       COUNT(TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY')) AS US_PARSED,
       COUNT(TRY_TO_DATE(DATE_TXT, 'DD/MM/YYYY')) AS UK_PARSED,
       COUNT_IF(TRY_TO_DATE(DATE_TXT, 'DD/MM/YYYY') IS NULL) AS UK_REJECTED_LOUDLY,
       COUNT_IF(TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY') IS NOT NULL
                AND TRY_TO_DATE(DATE_TXT, 'DD/MM/YYYY') IS NOT NULL
                AND TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY')
                    <> TRY_TO_DATE(DATE_TXT, 'DD/MM/YYYY')) AS SILENTLY_DIFFERENT
FROM SALES_RAW
WHERE REGEXP_LIKE(DATE_TXT, '[0-9]{2}/[0-9]{2}/[0-9]{4}');
Result1 row · 5 columns
SLASH_FORMAT_ROWSUS_PARSEDUK_PARSEDUK_REJECTED_LOUDLYSILENTLY_DIFFERENT
22020

Two slash-format rows. Both parsed month-first. Two were rejected loudly when read day-first, and zero were silently different.

Read that zero carefully

That zero is luck, not safety. Both of those days are 28 and 21, and both are above 12, so the wrong reading had nowhere to go. One row dated on the 5th of something and it would have parsed both ways with nothing to show for it. The count is worth running. It is not worth trusting as a property of your pipeline.

Can we just let Snowflake work the format out?

Yes, and it is better at it than you might expect. There is a session parameter that controls this, so let's look at it before we use it.

Query
SHOW PARAMETERS LIKE 'DATE_INPUT_FORMAT' IN SESSION;
Result1 row · 6 columns · scroll →
keyvaluedefaultleveldescriptiontype
DATE_INPUT_FORMATAUTOAUTOinput format for dateSTRING

The value is AUTO, and AUTO is also the default. So on a fresh session this is what you already have.

That means we can call TRY_TO_DATE with no format at all. Let's do that on the whole column, all three shapes together.

Query
SELECT ROW_ID, DATE_TXT,
       TRY_TO_DATE(DATE_TXT) AS AUTO_PARSED
FROM SALES_RAW
ORDER BY ROW_ID;
Result8 rows · 3 columns
ROW_IDDATE_TXTAUTO_PARSED
12023-01-122023-01-12
201/28/20232023-01-28
314-Feb-20232023-02-14
42023-03-092023-03-09
52023-04-052023-04-05
605/21/20232023-05-21
714-Aug-20232023-08-14
82024-09-292024-09-29

Every row parsed. ISO, slashes and month names, all handled, with no format string anywhere in the query.

Let's put the comparison in one row, so we can see what AUTO does that a named format does not.

Query
SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(TRY_TO_DATE(DATE_TXT))                     AS AUTO_SUCCEEDED,
       COUNT(TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD'))       AS ISO_FORMAT_SUCCEEDED,
       COUNT(TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY'))       AS US_FORMAT_SUCCEEDED,
       COUNT(TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY'))      AS MON_FORMAT_SUCCEEDED
FROM SALES_RAW;
Result1 row · 5 columns
ROWS_TOTALAUTO_SUCCEEDEDISO_FORMAT_SUCCEEDEDUS_FORMAT_SUCCEEDEDMON_FORMAT_SUCCEEDED
88422

AUTO got all eight. The ISO format got four, the month-first slash format got two, and the month-name format got two. Each named format handles its own rows and nothing else.

Convenient is not the same as safe

AUTO is convenient and it is also what decides an ambiguous date for you, without writing the decision down anywhere. On anything you care about, name the format. Then the decision is yours and it is visible in the query.

Now let's convert the whole column for real

Here is the pattern that actually ships. COALESCE over three TRY_TO_DATE calls, one per format, in the order we want them tried. The first one that works wins.

Query
SELECT ROW_ID, DATE_TXT,
       COALESCE(TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD'),
                TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY'),
                TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY')) AS THE_DATE
FROM SALES_RAW
ORDER BY ROW_ID;
Result8 rows · 3 columns
ROW_IDDATE_TXTTHE_DATE
12023-01-122023-01-12
201/28/20232023-01-28
314-Feb-20232023-02-14
42023-03-092023-03-09
52023-04-052023-04-05
605/21/20232023-05-21
714-Aug-20232023-08-14
82024-09-292024-09-29

All eight rows converted. And because we chose the order ourselves, we know which rule matched each row rather than trusting AUTO to pick.

Let's count that instead of eyeballing eight rows.

Query
SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(COALESCE(TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD'),
                      TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY'),
                      TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY'))) AS CONVERTED,
       COUNT(*) - COUNT(COALESCE(TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD'),
                      TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY'),
                      TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY'))) AS STILL_TEXT
FROM SALES_RAW;
Result1 row · 3 columns
ROWS_TOTALCONVERTEDSTILL_TEXT
880

Eight rows in, eight converted, nothing left as text.

And now that it is a real date, everything from the last three posts works on it. Let's put a day name and a month start beside it.

Query
SELECT ROW_ID, DATE_TXT,
       COALESCE(TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD'),
                TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY'),
                TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY')) AS THE_DATE,
       DAYNAME(COALESCE(TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD'),
                TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY'),
                TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY'))) AS DAY_NAME,
       DATE_TRUNC('month', COALESCE(TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD'),
                TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY'),
                TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY'))) AS MONTH_START
FROM SALES_RAW
ORDER BY THE_DATE;
Result8 rows · 5 columns
ROW_IDDATE_TXTTHE_DATEDAY_NAMEMONTH_START
12023-01-122023-01-12Thu2023-01-01
201/28/20232023-01-28Sat2023-01-01
314-Feb-20232023-02-14Tue2023-02-01
42023-03-092023-03-09Thu2023-03-01
52023-04-052023-04-05Wed2023-04-01
605/21/20232023-05-21Sun2023-05-01
714-Aug-20232023-08-14Mon2023-08-01
82024-09-292024-09-29Sun2024-09-01

DAYNAME and DATE_TRUNC on a column that was text a minute ago. Nothing special had to happen. It is a date now, so the date functions work.

Why bother? Because sorting text is not sorting dates

Here is the reason this matters even when nothing fails. Let's number the rows two ways in one query: once ordered by the text, once ordered by the parsed date.

Query
SELECT ROW_ID, DATE_TXT,
       ROW_NUMBER() OVER (ORDER BY DATE_TXT) AS TEXT_ORDER,
       ROW_NUMBER() OVER (ORDER BY COALESCE(TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD'),
                TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY'),
                TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY'))) AS DATE_ORDER
FROM SALES_RAW
ORDER BY DATE_ORDER;
Result8 rows · 4 columns
ROW_IDDATE_TXTTEXT_ORDERDATE_ORDER
12023-01-1251
201/28/202312
314-Feb-202343
42023-03-0964
52023-04-0575
605/21/202326
714-Aug-202337
82024-09-2988

Look at the first row. Position 5 as text, position 1 as a date. As text, 01/28 sorts before 2023, because 0 comes before 2.

Let's count how many rows moved.

Query
SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(T.TEXT_ORDER <> T.DATE_ORDER) AS IN_A_DIFFERENT_POSITION
FROM (SELECT ROW_NUMBER() OVER (ORDER BY DATE_TXT) AS TEXT_ORDER,
             ROW_NUMBER() OVER (ORDER BY COALESCE(TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD'),
                TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY'),
                TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY'))) AS DATE_ORDER
      FROM SALES_RAW) T;
Result1 row · 2 columns
ROWS_TOTALIN_A_DIFFERENT_POSITION
87

Eight rows, and seven of them sit in a different position. Only the last row happens to land in the same place in both orderings.

That is the whole argument for converting. A date-shaped string sorts alphabetically, and alphabetical is not chronological unless every row in the column happens to be ISO.

Now let's parse a timestamp, and an epoch

TO_TIMESTAMP and TRY_TO_TIMESTAMP work exactly like the date pair, with time elements in the format as well.

Query
SELECT TO_TIMESTAMP('2024-06-30 14:37:52', 'YYYY-MM-DD HH24:MI:SS') AS PARSED,
       TO_TIMESTAMP('30/06/2024 02:37 PM', 'DD/MM/YYYY HH12:MI AM')  AS TWELVE_HOUR,
       TRY_TO_TIMESTAMP('2024-06-30', 'YYYY-MM-DD')                  AS DATE_ONLY,
       TRY_TO_TIMESTAMP('nonsense', 'YYYY-MM-DD HH24:MI:SS')         AS RUBBISH;
Result1 row · 4 columns
PARSEDTWELVE_HOURDATE_ONLYRUBBISH
2024-06-30 14:37:522024-06-30 14:37:002024-06-30 00:00:00NULL

The first column parsed the full timestamp. The second used the twelve-hour clock, and because the format has no SS in it the seconds came back as 00. The third gave a timestamp at midnight from date-only text. And the last one is rubbish, so TRY_TO_TIMESTAMP gave NULL.

TO_TIMESTAMP has one more trick. Hand it a number instead of text and it reads it as seconds since the epoch.

Query
SELECT TO_TIMESTAMP(0)                                                AS EPOCH_ZERO,
       TO_TIMESTAMP(1719757072)                                       AS EPOCH_SECONDS,
       TO_TIMESTAMP(1719757072000, 3)                                 AS EPOCH_MILLIS,
       DATE_PART('epoch_second', '2024-06-30 14:37:52'::TIMESTAMP_NTZ) AS BACK_TO_EPOCH;
Result1 row · 4 columns
EPOCH_ZEROEPOCH_SECONDSEPOCH_MILLISBACK_TO_EPOCH
1970-01-01 00:00:002024-06-30 14:17:522024-06-30 14:17:521719758272

TO_TIMESTAMP(0) is the start of 1970, which is where the counting begins. The second and third columns are the same instant written two ways, once in seconds and once in milliseconds with a scale argument of 3.

The last column goes back the other way, turning a timestamp into an epoch second. Notice it does not match the number in the second column — those are two different instants, twenty minutes apart. The epoch literal in this query is not the epoch of the timestamp beside it.

One last thing: does a round trip survive?

If we format a date to text and then parse that text straight back, do we get the same date? Let's not assume. Let's check it on the clean ORDERS table.

Query
SELECT ORDER_ID, ORDER_DATE,
       TO_CHAR(ORDER_DATE, 'DD-MON-YYYY')                      AS FORMATTED,
       TO_DATE(TO_CHAR(ORDER_DATE, 'DD-MON-YYYY'), 'DD-MON-YYYY') AS BACK_AGAIN,
       ORDER_DATE = TO_DATE(TO_CHAR(ORDER_DATE, 'DD-MON-YYYY'), 'DD-MON-YYYY')
         AS SURVIVED
FROM ORDERS
ORDER BY ORDER_ID
LIMIT 10;
Result10 rows · 5 columns
ORDER_IDORDER_DATEFORMATTEDBACK_AGAINSURVIVED
10012023-01-1212-Jan-20232023-01-12TRUE
10022023-01-2828-Jan-20232023-01-28TRUE
10032023-02-1414-Feb-20232023-02-14TRUE
10042023-02-2727-Feb-20232023-02-27TRUE
10052023-03-0909-Mar-20232023-03-09TRUE
10062023-03-2222-Mar-20232023-03-22TRUE
10072023-04-0505-Apr-20232023-04-05TRUE
10082023-04-1919-Apr-20232023-04-19TRUE
10092023-05-0202-May-20232023-05-02TRUE
10102023-05-2121-May-20232023-05-21TRUE

The SURVIVED column is TRUE on every row of the grid. Out and back through DD-MON-YYYY and nothing moved.

That grid is only the first part of the table though, so let's check all of it with a count.

Query
SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(ORDER_DATE = TO_DATE(TO_CHAR(ORDER_DATE, 'DD-MON-YYYY'), 'DD-MON-YYYY'))
         AS SURVIVED,
       COUNT_IF(ORDER_DATE <> TO_DATE(TO_CHAR(ORDER_DATE, 'YYYY-MM-DD'), 'YYYY-MM-DD'))
         AS ISO_ROUND_TRIP_FAILURES
FROM ORDERS;
Result1 row · 3 columns
ROWS_TOTALSURVIVEDISO_ROUND_TRIP_FAILURES
41410

Forty-one orders, forty-one survived, and the ISO round trip had no failures either.

So a round trip through a format you named is safe. A round trip through AUTO is a round trip through a guess, and on an ambiguous date the guess is the whole answer.

Let's finish with one grid that does the whole job: raw text in, a real date in the middle, and two formatted columns out.

Query
SELECT ROW_ID, DATE_TXT,
       COALESCE(TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD'),
                TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY'),
                TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY')) AS THE_DATE,
       TO_CHAR(COALESCE(TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD'),
                TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY'),
                TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY')), 'DY DD MON YYYY') AS READABLE,
       TO_CHAR(COALESCE(TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD'),
                TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY'),
                TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY')), 'YYYY-MM-DD') AS NORMALISED
FROM SALES_RAW
ORDER BY ROW_ID;
Result8 rows · 5 columns
ROW_IDDATE_TXTTHE_DATEREADABLENORMALISED
12023-01-122023-01-12Thu 12 Jan 20232023-01-12
201/28/20232023-01-28Sat 28 Jan 20232023-01-28
314-Feb-20232023-02-14Tue 14 Feb 20232023-02-14
42023-03-092023-03-09Thu 09 Mar 20232023-03-09
52023-04-052023-04-05Wed 05 Apr 20232023-04-05
605/21/20232023-05-21Sun 21 May 20232023-05-21
714-Aug-20232023-08-14Mon 14 Aug 20232023-08-14
82024-09-292024-09-29Sun 29 Sep 20242024-09-29

Three date shapes in, one normalised date out, and a readable version beside it. That is the shape of the work: parse once into a real date, then format for whoever is reading.

Key points

  • TO_CHAR turns a date into text. TO_DATE turns text into a date. Both take a format string.
  • In a time format, MM is the month and MI is the minutes. Getting that wrong gives you a plausible clock time, no error, and a wrong answer on every row.
  • HH12 needs AM in the format with it, and the half hour after midnight reads 12:30 AM.
  • TO_CHAR on a number uses a completely different format language. Date elements mean nothing there.
  • TO_DATE throws when the text does not fit, which stops the whole query. TRY_TO_DATE returns NULL instead.
  • TRY_TO_DATE checks the calendar as well as the shape. The 30th of February is NULL, and so is the 29th of February in a year that does not have one.
  • A slash date with a day above 12 fails loudly when you read it the wrong way round. A day of 12 or under does not. It parses and gives you a different day.
  • On our two slash-format rows, zero were silently misread — but only because both of those days are above 12. That is luck, not a property of the pipeline.
  • DATE_INPUT_FORMAT is AUTO by default, and AUTO parsed all eight of our rows while each named format parsed only its own. Naming the format is how you keep the decision.
  • COALESCE over several TRY_TO_DATE calls converts a mixed column and lets you choose the order the rules are tried in.
  • Sorting date-shaped text is not sorting dates. Seven of our eight rows landed in a different position.
  • A round trip through a named format survived on all 41 orders. A round trip through AUTO is a round trip through a guess.
Next in this series

Episode 76 — time zones. TIMESTAMP_NTZ, TIMESTAMP_LTZ and TIMESTAMP_TZ, what the session time zone actually controls, and one cast that moves your data without saying a word.

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 SALES_RAW and ORDERS data is invented demo data created by the setup script above. Every date, clock time and weekday named in the text is read off a captured column rather than worked out here. Nothing on this page is timed and no performance claims are made anywhere in this series. Function behaviour follows the Snowflake conversion functions documentation.

No comments:

Post a Comment

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

Subscribe to: Post Comments (Atom)