Snowflake snippets
100 Practical Examples of String to Date in Snowflake
In Snowflake, converting strings to date or time formats is a common task that allows you to handle and analyze time-series data more effectively. Here are the key functions you'll use:
- TO_DATE(): Converts a string to a date.
- TO_TIME(): Converts a string to a time.
- TO_TIMESTAMP(): Converts a string to a timestamp (which includes date and time).
- TRY_TO_DATE()
- TRY_TO_TIME()
- TRY_TO_TIMESTAMP()
These last three functions are helpful for handling data with potential format inconsistencies, allowing for safer conversions without runtime errors.
TO_DATE(string, format)
This function is used when you have a string representing only a date. The basic syntax is:
SELECT TO_DATE('2024-07-18', 'YYYY-MM-DD');
Here, `'2024-07-18'` is the string to convert, and `'YYYY-MM-DD'` is the format of the string.
TO_TIME(string, format)
This function is used when your string represents only a time. The basic syntax is:
SELECT TO_TIME('13:45:30', 'HH24:MI:SS');
Here, `'13:45:30'` is the string to convert, and `'HH24:MI:SS'` is the format of the string.
TO_TIMESTAMP(string, format)
This function is used when your string represents both date and time. The basic syntax is:
SELECT TO_TIMESTAMP('2024-07-18 13:45:30', 'YYYY-MM-DD HH24:MI:SS');
Here, `'2024-07-18 13:45:30'` is the string to convert, and `'YYYY-MM-DD HH24:MI:SS'` is the string format.
TRY_TO_DATE(string, format)
This function attempts to convert a string to a date. If the conversion fails due to an invalid format or value, it returns `NULL` instead of an error.
Example:
SELECT TRY_TO_DATE('2024-07-18', 'YYYY-MM-DD'); -- Returns a date
SELECT TRY_TO_DATE('invalid-date', 'YYYY-MM-DD'); -- Returns NULL
TRY_TO_TIME(string, format)
This function attempts to convert a string to a time. If the conversion fails, it returns `NULL`.
Example:
SELECT TRY_TO_TIME('13:45:30', 'HH24:MI:SS'); -- Returns a time
SELECT TRY_TO_TIME('invalid-time', 'HH24:MI:SS'); -- Returns NULL
TRY_TO_TIMESTAMP(string, format)
This function attempts to convert a string to a timestamp. If the conversion fails, it returns `NULL`.
Example:
SELECT TRY_TO_TIMESTAMP('2024-07-18 13:45:30', 'YYYY-MM-DD HH24:MI:SS'); -- Returns a timestamp
SELECT TRY_TO_TIMESTAMP('invalid-timestamp', 'YYYY-MM-DD HH24:MI:SS'); -- Returns NULL
TO_DATE() example use case
Here are 40 examples of using the `TO_DATE()` function in Snowflake, showcasing various date formats and edge cases:
SELECT TO_DATE('2024-07-18', 'YYYY-MM-DD');
SELECT TO_DATE('07/18/2024', 'MM/DD/YYYY');
SELECT TO_DATE('18-Jul-2024', 'DD-Mon-YYYY');
SELECT TO_DATE('2024.07.18', 'YYYY.MM.DD');
SELECT TO_DATE('20240718', 'YYYYMMDD');
SELECT TO_DATE('18th July 2024', 'DDth Month YYYY');
SELECT TO_DATE('18-July-24', 'DD-Month-YY');
SELECT TO_DATE('Thursday, July 18, 2024', 'Day, Month DD, YYYY');
SELECT TO_DATE('18/07/24', 'DD/MM/YY');
SELECT TO_DATE('2024/07/18', 'YYYY/MM/DD');
SELECT TO_DATE('Jul-18-2024', 'Mon-DD-YYYY');
SELECT TO_DATE('2024-Jul-18', 'YYYY-Mon-DD');
SELECT TO_DATE('2024 July 18', 'YYYY Month DD');
SELECT TO_DATE('18-07-2024', 'DD-MM-YYYY');
SELECT TO_DATE('2024-199', 'YYYY-DDD'); -- Day of the year
SELECT TO_DATE('24-199', 'YY-DDD'); -- Day of the year with two-digit year
SELECT TO_DATE('202407', 'YYYYMM'); -- Year and month
SELECT TO_DATE('2024-W29-4', 'YYYY-"W"IW-D'); -- ISO week date
SELECT TO_DATE('18-July', 'DD-Month'); -- Day and month
SELECT TO_DATE('July 2024', 'Month YYYY'); -- Month and year
SELECT TO_DATE('2024 July', 'YYYY Month'); -- Year and month
SELECT TO_DATE('18-Jul-2024', 'DD-Mon-YYYY');
SELECT TO_DATE('2024/199', 'YYYY/DDDD'); -- Day of the year with slashes
SELECT TO_DATE('18-07', 'DD-MM'); -- Day and month with hyphens
SELECT TO_DATE('July 18, 2024', 'Month DD, YYYY');
SELECT TO_DATE('18th of July, 2024', 'DDth of Month, YYYY');
SELECT TO_DATE('July 18 2024', 'Month DD YYYY');
SELECT TO_DATE('18-2024-07', 'DD-YYYY-MM'); -- Uncommon format
SELECT TO_DATE('2024/July/18', 'YYYY/Month/DD');
SELECT TO_DATE('18 July 24', 'DD Month YY');
SELECT TO_DATE('18.July.2024', 'DD.Month.YYYY');
SELECT TO_DATE('July.18.2024', 'Month.DD.YYYY');
SELECT TO_DATE('2024-07', 'YYYY-MM'); -- Year and month
SELECT TO_DATE('07-2024', 'MM-YYYY'); -- Month and year
SELECT TO_DATE('20241807', 'YYYYDDMM'); -- Swapped day and month
SELECT TO_DATE('18-JUL-24', 'DD-MON-YY'); -- Uppercase month abbreviation
SELECT TO_DATE('18.jul.2024', 'DD.mon.YYYY'); -- Lowercase month abbreviation
SELECT TO_DATE('July-2024-18', 'Month-YYYY-DD');
SELECT TO_DATE('2024-07-18T00:00:00Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'); -- ISO 8601 format with time
SELECT TO_DATE('2024-07-18 13:45:30', 'YYYY-MM-DD HH24:MI:SS'); -- Date and time with spaces
TO_TIME() example use case
Here are 20 examples of using the `TO_TIME()` function in Snowflake, illustrating various time formats:
SELECT TO_TIME('13:45:30', 'HH24:MI:SS');
SELECT TO_TIME('01:45:30 PM', 'HH:MI:SS AM');
SELECT TO_TIME('01:45 PM', 'HH:MI AM');
SELECT TO_TIME('13:45', 'HH24:MI');
SELECT TO_TIME('1:45:30.123 PM', 'HH:MI:SS.FF3 AM');
SELECT TO_TIME('13:45:30.123456', 'HH24:MI:SS.FF6');
SELECT TO_TIME('1:45:30 PM', 'HH:MI:SS PM');
SELECT TO_TIME('134530', 'HH24MISS');
SELECT TO_TIME('1:45', 'HH:MI');
SELECT TO_TIME('01:45 PM', 'HH12:MI PM');
SELECT TO_TIME('01:45:30.123456 PM', 'HH:MI:SS.FF6 AM');
SELECT TO_TIME('13.45.30', 'HH24.MI.SS');
SELECT TO_TIME('01-45-30 PM', 'HH-MI-SS AM');
SELECT TO_TIME('1:45:30 p.m.', 'HH:MI:SS am.');
SELECT TO_TIME('13:45:30:123', 'HH24:MI:SS:FF3');
SELECT TO_TIME('1:45:30 pm', 'HH:MI:SS pm');
SELECT TO_TIME('13-45-30', 'HH24-MI-SS');
SELECT TO_TIME('01.45.30 PM', 'HH.MI.SS AM');
SELECT TO_TIME('1:45:30 p.m.', 'HH:MI:SS a.m.');
SELECT TO_TIME('01:45 PM', 'HH:MI PM');
TO_TIMESTAMP() example use case
Here are 40 examples of using the `TO_TIMESTAMP()` function in Snowflake, illustrating various datetime formats:
SELECT TO_TIMESTAMP('2024-07-18 13:45:30', 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_TIMESTAMP('07/18/2024 01:45:30 PM', 'MM/DD/YYYY HH:MI:SS AM');
SELECT TO_TIMESTAMP('18-Jul-2024 13:45', 'DD-Mon-YYYY HH24:MI');
SELECT TO_TIMESTAMP('2024.07.18 13:45:30.123456', 'YYYY.MM.DD HH24:MI:SS.FF6');
SELECT TO_TIMESTAMP('20240718134530', 'YYYYMMDDHH24MISS');
SELECT TO_TIMESTAMP('18th July 2024 1:45 PM', 'DDth Month YYYY HH:MI AM');
SELECT TO_TIMESTAMP('18-July-24 13:45:30', 'DD-Month-YY HH24:MI:SS');
SELECT TO_TIMESTAMP('Thursday, July 18, 2024 13:45', 'Day, Month DD, YYYY HH24:MI');
SELECT TO_TIMESTAMP('18/07/24 13:45:30', 'DD/MM/YY HH24:MI:SS');
SELECT TO_TIMESTAMP('2024/07/18 13:45:30', 'YYYY/MM/DD HH24:MI:SS');
SELECT TO_TIMESTAMP('Jul-18-2024 01:45 PM', 'Mon-DD-YYYY HH:MI AM');
SELECT TO_TIMESTAMP('2024-Jul-18 13:45', 'YYYY-Mon-DD HH24:MI');
SELECT TO_TIMESTAMP('2024 July 18 13:45:30', 'YYYY Month DD HH24:MI:SS');
SELECT TO_TIMESTAMP('18-07-2024 13:45:30', 'DD-MM-YYYY HH24:MI:SS');
SELECT TO_TIMESTAMP('2024-199 13:45:30', 'YYYY-DDD HH24:MI:SS'); -- Day of the year
SELECT TO_TIMESTAMP('24-199 13:45', 'YY-DDD HH24:MI'); -- Day of the year with two-digit year
SELECT TO_TIMESTAMP('202407 13:45', 'YYYYMM HH24:MI'); -- Year and month with time
SELECT TO_TIMESTAMP('2024-W29-4 13:45:30', 'YYYY-"W"IW-D HH24:MI:SS'); -- ISO week date
SELECT TO_TIMESTAMP('18-July 13:45', 'DD-Month HH24:MI'); -- Day and month with time
SELECT TO_TIMESTAMP('July 2024 13:45', 'Month YYYY HH24:MI'); -- Month and year with time
SELECT TO_TIMESTAMP('2024 July 18 01:45 PM', 'YYYY Month DD HH:MI AM');
SELECT TO_TIMESTAMP('18-Jul-2024 01:45 PM', 'DD-Mon-YYYY HH:MI AM');
SELECT TO_TIMESTAMP('2024/199 13:45', 'YYYY/DDDD HH24:MI'); -- Day of the year with slashes
SELECT TO_TIMESTAMP('18-07 13:45', 'DD-MM HH24:MI'); -- Day and month with time
SELECT TO_TIMESTAMP('July 18, 2024 13:45:30', 'Month DD, YYYY HH24:MI:SS');
SELECT TO_TIMESTAMP('18th of July, 2024 01:45 PM', 'DDth of Month, YYYY HH:MI AM');
SELECT TO_TIMESTAMP('July 18 2024 13:45:30', 'Month DD YYYY HH24:MI:SS');
SELECT TO_TIMESTAMP('18-2024-07 13:45', 'DD-YYYY-MM HH24:MI'); -- Uncommon format with time
SELECT TO_TIMESTAMP('2024/July/18 13:45:30', 'YYYY/Month/DD HH24:MI:SS');
SELECT TO_TIMESTAMP('18 July 24 13:45', 'DD Month YY HH24:MI');
SELECT TO_TIMESTAMP('18.July.2024 13:45:30', 'DD.Month.YYYY HH24:MI:SS');
SELECT TO_TIMESTAMP('July.18.2024 13:45', 'Month.DD.YYYY HH24:MI');
SELECT TO_TIMESTAMP('2024-07 13:45:30', 'YYYY-MM HH24:MI:SS'); -- Year and month with time
SELECT TO_TIMESTAMP('07-2024 13:45', 'MM-YYYY HH24:MI'); -- Month and year with time
SELECT TO_TIMESTAMP('20241807 13:45', 'YYYYDDMM HH24:MI'); -- Swapped day and month with time
SELECT TO_TIMESTAMP('18-JUL-24 01:45 PM', 'DD-MON-YY HH:MI AM'); -- Uppercase month abbreviation with time
SELECT TO_TIMESTAMP('18.jul.2024 13:45:30', 'DD.mon.YYYY HH24:MI:SS'); -- Lowercase month abbreviation with time
SELECT TO_TIMESTAMP('July-2024-18 13:45', 'Month-YYYY-DD HH24:MI');
SELECT TO_TIMESTAMP('2024-07-18T13:45:30Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'); -- ISO 8601 format with time
SELECT TO_TIMESTAMP('2024-07-18 13:45:30', 'YYYY-MM-DD HH24:MI:SS'); -- Date and time with spaces
Format reference list
Please also refer to the official guide web page.
Here's a complete list of format possibilities in Snowflake, presented in a table with the format, explanation, and examples.
| Format | Explanation | Example(s) |
|---|---|---|
| `YYYY` | Four-digit year | `2024` |
| `YY` | Two-digit year | `24` |
| `MM` | Two-digit month (01-12) | `07` |
| `MON` | Abbreviated month name | `Jul` |
| `MONTH` | Full month name | `July` |
| `DD` | Two-digit day of the month (01-31) | `18` |
| `DY` | Abbreviated day of the week | `Thu` |
| `DAY` | Full day of the week | `Thursday` |
| `HH24` | Hour of the day (00-23) | `13` |
| `HH` or `HH12` | Hour of the day (01-12) | `01` |
| `MI` | Minute (00-59) | `45` |
| `SS` | Second (00-59) | `30` |
| `FF` | Fractional seconds | `123456` (6 fractional digits) |
| `AM`, `PM` | Meridian indicator | `PM` |
| `Q` | Quarter of the year (1-4) | `3` |
| `RM` | Roman numeral month | `VII` |
| `WW` | Week of the year (1-53) | `29` |
| `W` | Week of the month (1-5) | `3` |
| `DDD` | Day of the year (1-366) | `199` |
| `DAYSP` | Full day name in Spanish | `jueves` |
| `J` | Julian date (days since 4713 BC) | `2459353` |
| `TZHTZM` | Time zone hour and minute | `+0530` |
| `TZHTZMM` | Time zone hour and minute (minutes in two digits) | `+05:30` |
| `TZD` | Time zone abbreviation | `PST` |
| `TZO` | Time zone offset from UTC | `-8` |
| `CC` | Century | `20` for 2000-2099 |
| `SYYYY` | Signed four-digit year | `+2024` or `-0024` |
| `BC`, `AD` | Era indicator | `AD` |
| `FM` | Fill mode (removes leading and trailing blanks) | `FMYYYY-MM-DD` (`2024-7-18` for `2024-07-18`) |
| `SP` | Spell out numbers | `18TH JULY 2024` (for `DDSPTH MONTH YYYY`) |
| `TH` | Ordinal number suffix | `18TH` (for `18`) |
| `Y,YYY` | Year with comma | `2,024` |
These format specifiers can be combined to handle various date and time string formats in Snowflake, allowing for accurate and flexible conversions.