Snowflake Date Format Conversion Functions and Examples

The date data types are one of the complicated types in the relational databases. Date types stores year, month, days, hours, minutes, seconds and nanoseconds. The date types also store the timezone information. Snowflake provides many date conversion functions, you can use those to format the date type.

Snowflake Date and Time Data Types

The Snowflake Date format includes four data types, and are used to store the date, time with timestamp details:

DATE: You can use the date type to store year, month, day.
TIME: You can use time type to store hour, minute, second, fraction with (includes 6 decimal positions).
TIMESTAMP: for year, month, day, hour, minute, second, fraction (includes 6 decimal positions).
TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ: This type is same as TIME, but, includes time zone information.

Snowflake Date Format Conversion Functions

Similar to other relational databases, Snowflake supports many date and time conversion functions. These functions can be used to convert string containing date format to date or timestamp.

Following are date conversion functions available in Snowflake.

Conversion Function	Description
TO_DATE	Convert string containing date to date type.
TO_TIME	Convert string containing time to time type.
TO_TIMESTAMP	Convert string containing timestamp to timestamp type.
CAST	Convert string containing date to date or date time format.
::	The cast operator is alternative to CAST function.

You can use any of the above mentioned formats conversion functions as per your requirement and expression.

Convert String to Date Format using Snowflake CAST Function and CAST Operator (::)

The Snowflake CAST function or CAST operator is used to convert a value of one data type into another data type.

For example, consider following example to convert string containing date to date type using the CAST function and operator.

select CAST('2020-01-01' as DATE) as dt;
+------------+
| DT         |
|------------|
| 2020-01-01 |
+------------+

select '2020-01-01'::DATE as dt;
+------------+
| DT         |
|------------|
| 2020-01-01 |
+------------+

Convert a String to Date format using Snowflake TO_DATE Function

Following is an example to use Snowflake TO_DATE function to convert string having date to date format.

Select TO_DATE( '2020-12-31 23:45:58','YYYY-MM-DD HH24:MI:SS') as dt;
+------------+
| DT         |
|------------|
| 2020-12-31 |
+------------+

Convert a String to time format using Snowflake TO_TIME Function

Following is an example to convert string to time using TO_TIME function.

Select TO_TIME( '23:45:58','HH24:MI:SS') as tm;
+----------+
| TM       |
|----------|
| 23:45:58 |
+----------+

Convert a String to timestamp format using Snowflake TO_TIMESTAMP Function

Following is an example to use Snowflake TO_TIMESTAMP function to convert string having date time to Snowflake timestamp format.

Select TO_TIMESTAMP( '2020-12-31 23:45:58','YYYY-MM-DD HH24:MI:SS') as dt;
+-------------------------+
| DT                      |
|-------------------------|
| 2020-12-31 23:45:58.000 |
+-------------------------+

Convert Integer (YYYYMMDD) to date format in Snowflake

Following is an example to convert integer value from ‘YYYYMMDD’ format to date format.

select to_date('20161022', 'YYYYMMDD') as dt;
+------------+
| DT         |
|------------|
| 2016-10-22 |
+------------+

Convert Current Date to Integer (YYYYMMDD) Format in Snowflake

Following example demonstrate on how to convert date to integer format.

select to_number(to_char(current_date,'YYYYMMDD'),'99999999') as INT;
+----------+
|      INT |
|----------|
| 20200112 |
+----------+