Snowflake turns a string into a date, time or timestamp with TO_DATE, TO_TIME and TO_TIMESTAMP. You pass the string and a format that describes it. Each function has a TRY_ variant that returns NULL instead of an error.

Snowflake's format elements are not Oracle's. Month, Day, DDth and IW fail, and DDD returns the wrong date without an error. An earlier version of this page used them too. Every example below was run on Snowflake, and the comments show the results it returned.

Copy and paste

Date in a known format:

SELECT TO_DATE('18/07/2024', 'DD/MM/YYYY');   -- 2024-07-18

Month names (MON and MMMM both read "Jul" and "July"):

SELECT TO_DATE('July 18, 2024', 'MMMM DD, YYYY');   -- 2024-07-18

Time with AM/PM (use HH12):

SELECT TO_TIME('01:45:30 PM', 'HH12:MI:SS AM');   -- 13:45:30

Timestamp:

SELECT TO_TIMESTAMP('2024-07-18 13:45:30', 'YYYY-MM-DD HH24:MI:SS');   -- 2024-07-18 13:45:30

ISO 8601 timestamp, keeping the offset:

SELECT TO_TIMESTAMP_TZ('2024-07-18T13:45:30+02:00', 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM');   -- 2024-07-18 13:45:30 +0200

NULL instead of an error:

SELECT TRY_TO_DATE('2024-02-30', 'YYYY-MM-DD');   -- NULL

A column with two formats:

SELECT COALESCE(TRY_TO_DATE(raw_date, 'YYYY-MM-DD'), TRY_TO_DATE(raw_date, 'DD/MM/YYYY')) AS order_date
FROM orders;

Day of the year (there is no format element for it):

SELECT DATE_FROM_PARTS(2024, 1, 199);   -- 2024-07-17

The functions

FunctionReturns
TO_DATE, TRY_TO_DATEDATE
TO_TIME, TRY_TO_TIMETIME
TO_TIMESTAMP, TRY_TO_TIMESTAMPTIMESTAMP_NTZ with the default TIMESTAMP_TYPE_MAPPING: no time zone
TO_TIMESTAMP_TZ, TRY_TO_TIMESTAMP_TZTIMESTAMP_TZ: keeps the offset

When the string does not match the format, the TO_ functions stop the query with an error such as Can't parse '18 July 24' as date with format 'DD Month YY'. The TRY_ functions return NULL for that row instead:

SELECT TRY_TO_DATE('2024-07-18', 'YYYY-MM-DD');      -- 2024-07-18
SELECT TRY_TO_DATE('invalid-date', 'YYYY-MM-DD');    -- NULL
SELECT TRY_TO_TIME('invalid-time', 'HH24:MI:SS');    -- NULL

Without a format, the functions use the session's input format: see strings without a format.

Format elements

Format elements are case-insensitive: MON, Mon and mon are the same. Every other character, such as -, / or a space, must appear in the string as it is. Put literal letters in double quotes ("T"), otherwise Snowflake may read them as elements.

ElementReadsExample
YYYYFour-digit year2024
YYTwo-digit year: 00–69 become 2000–2069, 70–99 become 1970–1999 (default TWO_DIGIT_CENTURY_START)24
MMMonth number, one or two digits07, 7
MONMonth name, abbreviated or full, English onlyJul, July, JUL
MMMMSame as MON when readingJuly, Jul
DDDay of the month, one or two digits18, 5
DDay of the month without a leading zero9
DYWeekday name, abbreviated or full. Not checked against the dateThu, Thursday
HH24Hour, 0–2313
HH12Hour, 1–12, with AM or PM01
AM, PMMeridian indicator, any case. Either element reads bothPM, pm
MIMinutes45
SSSeconds30
FF, FF1–FF9Fractional seconds. FF reads up to nine digits, FF3 up to three5, 123
TZH:TZMTime zone offset+02:00
"text"Literal text"T", "Z", "th"

The Snowflake documentation has the full list.

Dates

Numeric formats

SELECT TO_DATE('2024-07-18', 'YYYY-MM-DD');   -- 2024-07-18
SELECT TO_DATE('2024/07/18', 'YYYY/MM/DD');   -- 2024-07-18
SELECT TO_DATE('2024.07.18', 'YYYY.MM.DD');   -- 2024-07-18
SELECT TO_DATE('20240718', 'YYYYMMDD');       -- 2024-07-18
SELECT TO_DATE('18-07-2024', 'DD-MM-YYYY');   -- 2024-07-18
SELECT TO_DATE('18/07/2024', 'DD/MM/YYYY');   -- 2024-07-18
SELECT TO_DATE('18/07/24', 'DD/MM/YY');       -- 2024-07-18
SELECT TO_DATE('07/18/2024', 'MM/DD/YYYY');   -- 2024-07-18
SELECT TO_DATE('7/8/2024', 'MM/DD/YYYY');     -- 2024-07-08
SELECT TO_DATE('20241807', 'YYYYDDMM');       -- 2024-07-18

DD and MM also read single digits. The separators must match the format: TO_DATE('2024-07-18', 'YYYY/MM/DD') fails.

Month and weekday names

SELECT TO_DATE('18-Jul-2024', 'DD-MON-YYYY');                     -- 2024-07-18
SELECT TO_DATE('Jul-18-2024', 'MON-DD-YYYY');                     -- 2024-07-18
SELECT TO_DATE('18-JUL-24', 'DD-MON-YY');                         -- 2024-07-18
SELECT TO_DATE('18 July 2024', 'DD MMMM YYYY');                   -- 2024-07-18
SELECT TO_DATE('July 18, 2024', 'MMMM DD, YYYY');                 -- 2024-07-18
SELECT TO_DATE('September 5, 2024', 'MMMM DD, YYYY');             -- 2024-09-05
SELECT TO_DATE('2024/July/18', 'YYYY/MMMM/DD');                   -- 2024-07-18
SELECT TO_DATE('Thu, 18 Jul 2024', 'DY, DD MON YYYY');            -- 2024-07-18
SELECT TO_DATE('Thursday, July 18, 2024', 'DY, MMMM DD, YYYY');   -- 2024-07-18

MON and MMMM read both the abbreviation and the full name, in any case. Names must be in English: TO_DATE('18 Juli 2024', 'DD MMMM YYYY') fails.

DY skips the weekday without checking it. TO_DATE('Fri, 18 Jul 2024', 'DY, DD MON YYYY') returns 2024-07-18, which is a Thursday.

Ordinal suffixes

Quote the suffix when it is always the same. When it varies (1st, 2nd, 3rd, 4th), remove it first:

SELECT TO_DATE('18th July 2024', 'DD"th" MMMM YYYY');   -- 2024-07-18

SELECT TO_DATE(REGEXP_REPLACE('1st July 2024', '([0-9]+)(st|nd|rd|th)', '\\1'), 'DD MMMM YYYY');   -- 2024-07-01

SELECT TO_DATE(REGEXP_REPLACE('22nd of July, 2024', '([0-9]+)(st|nd|rd|th)( of)?', '\\1'), 'DD MMMM, YYYY');   -- 2024-07-22

Partial dates

SELECT TO_DATE('2024-07', 'YYYY-MM');       -- 2024-07-01
SELECT TO_DATE('202407', 'YYYYMM');         -- 2024-07-01
SELECT TO_DATE('07-2024', 'MM-YYYY');       -- 2024-07-01
SELECT TO_DATE('July 2024', 'MMMM YYYY');   -- 2024-07-01
SELECT TO_DATE('18-07', 'DD-MM');           -- 1970-07-18

A missing day becomes the 1st, and a missing year becomes 1970. If the year is implied, add it to the string:

SELECT TO_DATE('18-07' || '-' || YEAR(CURRENT_DATE()), 'DD-MM-YYYY');   -- 18 July of the current year

The date part of a timestamp string

A date-only format does not match a string that also has a time: TO_DATE('2024-07-18 13:45:30', 'YYYY-MM-DD') fails. Describe the whole string, or keep only the date part:

SELECT TO_DATE('2024-07-18 13:45:30', 'YYYY-MM-DD HH24:MI:SS');          -- 2024-07-18
SELECT TO_DATE(LEFT('2024-07-18 13:45:30', 10), 'YYYY-MM-DD');           -- 2024-07-18
SELECT TO_DATE('2024-07-18T00:00:00Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"');    -- 2024-07-18

Day of the year

Snowflake has no day-of-year element. It reads DDD as DD followed by D, both days of the month, so it returns the wrong date without an error:

SELECT TO_DATE('2024-199', 'YYYY-DDD');   -- 2024-01-09, not 2024-07-17

Build the date with DATE_FROM_PARTS instead. It accepts day 199 of January and moves forward into July:

SELECT DATE_FROM_PARTS(LEFT('2024-199', 4)::INT, 1, RIGHT('2024-199', 3)::INT);   -- 2024-07-17
SELECT DATE_FROM_PARTS(2023, 1, 199);                                             -- 2023-07-18, not a leap year

SELECT TIMESTAMP_NTZ_FROM_PARTS(DATE_FROM_PARTS(2024, 1, 199), TO_TIME('13:45:30', 'HH24:MI:SS'));   -- 2024-07-17 13:45:30

ISO week dates

There is no IW element either. For a string like 2024-W29-4 (year, ISO week, weekday from 1 for Monday to 7 for Sunday), start from 4 January, which is always in ISO week 1:

SELECT DATEADD(
         day,
         (SUBSTR(s, 7, 2)::INT - 1) * 7 + SUBSTR(s, 10, 1)::INT - DAYOFWEEKISO(DATE_FROM_PARTS(LEFT(s, 4)::INT, 1, 4)),
         DATE_FROM_PARTS(LEFT(s, 4)::INT, 1, 4)
       ) AS iso_date
FROM (SELECT '2024-W29-4' AS s);   -- 2024-07-18

The same query returns 2025-12-29 for 2026-W01-1, because week 1 of 2026 starts in December 2025, and 2021-01-01 for 2020-W53-5. Replace s with your column.

Times

SELECT TO_TIME('13:45:30', 'HH24:MI:SS');                 -- 13:45:30
SELECT TO_TIME('13:45', 'HH24:MI');                       -- 13:45:00
SELECT TO_TIME('134530', 'HH24MISS');                     -- 13:45:30
SELECT TO_TIME('13.45.30', 'HH24.MI.SS');                 -- 13:45:30
SELECT TO_TIME('13:45:30.123456', 'HH24:MI:SS.FF6');      -- 13:45:30.123456
SELECT TO_TIME('01:45:30 PM', 'HH12:MI:SS AM');           -- 13:45:30
SELECT TO_TIME('01:45 PM', 'HH12:MI AM');                 -- 13:45:00
SELECT TO_TIME('12:30 AM', 'HH12:MI AM');                 -- 00:30:00
SELECT TO_TIME('12:30 PM', 'HH12:MI AM');                 -- 12:30:00

Use HH12 with AM/PM. HH and HH24 accept an AM/PM marker too and add 12 hours for PM, although the documentation says not to combine them. HH then gets midnight wrong:

SELECT TO_TIME('12:30 AM', 'HH:MI AM');     -- 12:30:00, not 00:30:00
SELECT TO_TIME('12:30 AM', 'HH12:MI AM');   -- 00:30:00

HH12 rejects hours above 12, so TO_TIME('13:45', 'HH12:MI') fails. Snowflake does not read "a.m." and "p.m." with dots, or a single "A" or "P". Remove the dots first:

SELECT TO_TIME(REPLACE('1:45:30 p.m.', '.', ''), 'HH12:MI:SS AM');   -- 13:45:30

Timestamps

SELECT TO_TIMESTAMP('2024-07-18 13:45:30', 'YYYY-MM-DD HH24:MI:SS');              -- 2024-07-18 13:45:30
SELECT TO_TIMESTAMP('18/07/24 13:45:30', 'DD/MM/YY HH24:MI:SS');                  -- 2024-07-18 13:45:30
SELECT TO_TIMESTAMP('18-Jul-2024 13:45', 'DD-MON-YYYY HH24:MI');                  -- 2024-07-18 13:45:00
SELECT TO_TIMESTAMP('07/18/2024 01:45:30 PM', 'MM/DD/YYYY HH12:MI:SS AM');        -- 2024-07-18 13:45:30
SELECT TO_TIMESTAMP('July 18, 2024 1:45 PM', 'MMMM DD, YYYY HH12:MI AM');         -- 2024-07-18 13:45:00
SELECT TO_TIMESTAMP('20240718134530', 'YYYYMMDDHH24MISS');                        -- 2024-07-18 13:45:30
SELECT TO_TIMESTAMP('2024.07.18 13:45:30.123456', 'YYYY.MM.DD HH24:MI:SS.FF6');   -- 2024-07-18 13:45:30.123456
SELECT TO_TIMESTAMP('2024-07-18 13:45:30.5', 'YYYY-MM-DD HH24:MI:SS.FF');         -- 2024-07-18 13:45:30.5
SELECT TO_TIMESTAMP('2024-07-18 13:45:30,123', 'YYYY-MM-DD HH24:MI:SS,FF3');      -- 2024-07-18 13:45:30.123
SELECT TO_TIMESTAMP('2024-07-18', 'YYYY-MM-DD');                                  -- 2024-07-18 00:00:00

Time zones

TO_TIMESTAMP returns a TIMESTAMP_NTZ with the default settings, and drops an offset without warning:

SELECT TO_TIMESTAMP('2024-07-18T13:45:30+02:00');   -- 2024-07-18 13:45:30, the +02:00 is gone

Use TO_TIMESTAMP_TZ to keep it, and CONVERT_TIMEZONE to turn the result into UTC:

SELECT TO_TIMESTAMP_TZ('2024-07-18T13:45:30+02:00', 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM');   -- 2024-07-18 13:45:30 +0200

SELECT CONVERT_TIMEZONE('UTC', TO_TIMESTAMP_TZ('2024-07-18T13:45:30+02:00', 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM'))::TIMESTAMP_NTZ;   -- 2024-07-18 11:45:30

SELECT TO_TIMESTAMP_TZ('2024-07-18T13:45:30Z');   -- 2024-07-18 13:45:30 Z

In a format, "Z" is just a letter to skip: TO_TIMESTAMP('2024-07-18T13:45:30Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') returns 2024-07-18 13:45:30 as TIMESTAMP_NTZ. That is fine when every value is in UTC.

Unix timestamps

SELECT TO_TIMESTAMP('1721310330');        -- 2024-07-18 13:45:30, seconds in a string
SELECT TO_TIMESTAMP(1721310330000, 3);    -- 2024-07-18 13:45:30, milliseconds

Strings without a format

Without a format, Snowflake uses the session's DATE_INPUT_FORMAT or TIMESTAMP_INPUT_FORMAT, which are AUTO by default. AUTO reads ISO dates, and reads dates with slashes as month/day/year:

SELECT TO_DATE('2024-07-18');            -- 2024-07-18
SELECT TO_DATE('18-Jul-2024');           -- 2024-07-18
SELECT TO_DATE('07/18/2024');            -- 2024-07-18
SELECT TO_DATE('18/07/2024');            -- error
SELECT TRY_CAST('18/07/2024' AS DATE);   -- NULL

::DATE and CAST follow the same rules. For anything but ISO dates, pass the format.

Messy data

The TRY_ functions return NULL for values that are not real dates or times, and for values with extra characters:

SELECT TRY_TO_DATE('2024-02-30', 'YYYY-MM-DD');          -- NULL
SELECT TRY_TO_DATE('2023-02-29', 'YYYY-MM-DD');          -- NULL, 2023 is not a leap year
SELECT TRY_TO_DATE('2024-13-01', 'YYYY-MM-DD');          -- NULL
SELECT TRY_TO_TIME('24:00:00', 'HH24:MI:SS');            -- NULL
SELECT TRY_TO_DATE(' 2024-07-18 ', 'YYYY-MM-DD');        -- NULL, because of the spaces
SELECT TRY_TO_DATE(TRIM(' 2024-07-18 '), 'YYYY-MM-DD');  -- 2024-07-18

A wrong format also gives NULL, so a column full of NULLs usually means the format is wrong, not the data. To see the values that do not parse, filter on TRY_TO_DATE(...) IS NULL where the original column is not null.

When a column mixes formats, try them in order with COALESCE:

SELECT COALESCE(TRY_TO_DATE('18/07/2024', 'YYYY-MM-DD'), TRY_TO_DATE('18/07/2024', 'DD/MM/YYYY'));   -- 2024-07-18

The first format that matches wins, so keep formats that can be confused apart: 07/08/2024 matches both MM/DD/YYYY and DD/MM/YYYY.

Formats that fail or mislead

These formats come from Oracle and appear in many examples online. This is what Snowflake does with them:

FormatWhat Snowflake doesUse instead
Month, Day, as in 'Day, Month DD, YYYY'Error: Can't parseMMMM or MON, and DY
DDthErrorDD"th", or remove the suffix with REGEXP_REPLACE
DDD, DDDD (day of the year)Wrong date, no error: '2024-199' gives 2024-01-09DATE_FROM_PARTS
IW (ISO week)ErrorThe DATEADD query
a.m., p.m.ErrorRemove the dots, then HH12 with AM
HH or HH24 with AM/PMNot documented. With HH, 12 AM stays 12HH12
Day and month without a yearYear 1970: '18-07' gives 1970-07-18Add the year to the string

Tested with Snowflake 10.39.101 (October 2026). The comments show the results Snowflake returned.

References