DateTime conversions in Snowflake Cloud Data warehouse

Are you finding it difficult to operate on dates conversion in the Snowflake cloud data warehouse? You may have googled but didn’t get the help then you have landed up to the very right place. In this post, I will take you through all the variety of data time conversions in various formats. Let’s understand in detail how you can convert string to DateTime format vice versa and many other transformations.

Azure Databricks Tutorial

Azure Data Factory Tutorial for Beginners

Azure Databricks Tutorial for Beginners

How to get Current DateTime or Current TimeStamp in the Snowflake?

To get the current date time in Snowflake, you can use the following SQL expression:

-- To get the current date time in Snowflake using function : current_timestamp 
select current_timestamp 

Output:

2021-12-14 07:49:16.767 -0600

How to get Current Date in the Snowflake 

To get the current date in the Snowflake, you can use the following SQL code expression:

Assume current date time is 14 Dec 2021 9 PM

-- To get the current date in Snowflake use function : current_date
select current_date

Result : 2021-12-14

How to get current day in the Snowflake 

To get the current day (The name of the day of the week) in Snowflake, you can use the following code expression:

Assume current date time is 14 December 2021 9 PM

-- To get the name of the day in Snowflake using the date: dayname(date_column)
select dayname(current_date)

-- To get name of the day in Snowflake using the time stamp: dayname(timestamp_column)
select dayname(current_stamp)

Result : “Tue”

How to get day of the month in the Snowflake 

To get the day of the month (1-31) in Snowflake, you can use the following code expression:

Assume current date time is 14 December 2021 9 PM

Get the day of the month using the Date_Part function in snowflake

-- Day of the month using the Date_Format

select date_part(d,current_date);
select date_part(dd,current_date);
select date_part(days,current_date);
select date_part(dayofmonth,current_date);

-- All these will give same result.

Result: 14

How to get the day of the week in the Snowflake 

To get the day of the week (0-6) in Snowflake, you can use the following code expression:

Assume current date time is 14 December 2021 9 PM

-- To get the name of the day in Snowflake using the date: dayofweek(date_column)
select dayofweek(current_date)

-- To get day of the week in Snowflake using the timestamp: dayofweek(timestamp_column)
select dayofweek(current_timestamp)

Result : 2

Using DATE_PART function

To get the day of the week (0-6) in Snowflake using the date_part function, you can use the following code expression:

Assume current date time is 14 December 2021 9 PM

There are three more ways to get the day of the week using the date_part function as follows

-- Get the day of the week from the date or timestamp column using these 3 ways.

select date_part(weekday , current_date);

select date_part(dow, current_date);

select date_part(dw, current_date);

Result : 2

How to get the day of the year in the Snowflake 

To get the day of the year (0-365) in Snowflake, you can use the following code expression:

Assume current date time is 15 December 2021 9 PM

Using DATE_PART function

There are four ways to get the day of the year using the date_part function as follows

-- Get the day of the year from the date or timestamp column using these 4 ways.

select date_part(dy,current_date);
select date_part(yearday,current_date);
select date_part(doy,current_date);
select date_part(dayofyear,current_date););

-- All of them will give same result.

How to get the week of the year in the Snowflake 

To get the week of the year (1-53) in Snowflake, you can use the following code expression:

Assume current date time is 15 December 2021 9 PM

Using DATE_PART function

There are five ways to get the week of the year using the date_part function as follows

-- Get the week of the year from the date or timestamp column using these 5 ways.

select date_part(w,current_date);
select date_part(wk,current_date);
select date_part(weekofyear,current_date);
select date_part(woy,current_date);
select date_part(wy,current_date);

-- All of them will give same result.

Result: 50

How to get the quarter of the year in the Snowflake 

To get the quarter of the year (1-53) in Snowflake, you can use the following code expression:

Assume current date time is 15 December 2021 9 PM

Using DATE_PART function

There are 4 ways to get the quarter of the year using the date_part function as follows

-- Get the quarter of the year from the date or timestamp column using these 4 ways.

select date_part(q,current_date);
select date_part(qtr,current_date);
select date_part(qtrs,current_date);
select date_part(quarters,current_date);

-- All of them will give same result.

Result: 4

How to get the year of week in the Snowflake 

To get the year of the week (The year as a four-digit number) in Snowflake, you can use the following code expression:

Assume current date time is 15 December 2021 9 PM

Using DATE_PART function

There is one way to get the year of the week using the date_part function as follows. This is opposite of the week of the year.

-- Get the year of the week from the date or timestamp column using this way

select date_part(yearofweek ,current_date);

Result : 2021

How to get the hour from timestamp in the Snowflake 

To get the hour from the timestamp (0-23) in Snowflake, you can use the following code expression:

Assume current date time is 14 December 2021 2 AM

-- To get the hour in Snowflake using the timestamp: hour(timestamp_column)

select '2021-12-15T02:39:20.123-07:00'::timestamp as tstamp,
         hour(tstamp) as "HOUR"

Result : 2

Using DATE_PART function

To get the hour (0-23) in Snowflake using the date_part function, you can use the following code expression:

Assume current date time is 15 December 2021 2 AM

There are 5 more ways to get the hour from the timestamp column in Snowflake using the date_part function as follows

-- Get the day of the hour from timestamp column using these 5 ways.

select date_part(h,current_time);
select date_part(hh,current_time);
select date_part(hr,current_time);
select date_part(hours,current_time);
select date_part(hrs,current_time);

Result : 2

How to get the minute from timestamp in the Snowflake 

To get the minute from the timestamp (0-59) in Snowflake, you can use the following code expression:

Assume current date time is 14 December 2021 2 AM

-- To get the minute in Snowflake using the timestamp: minute (timestamp_column)

select '2021-12-15T02:39:20.123-07:00'::timestamp as tstamp,
         minute (tstamp) as "minute "

Result : 39

Using DATE_PART function

To get the minute (0-59) in Snowflake using the date_part function, you can use the following code expression:

Assume current date time is 15 December 2021 2 AM

There are 5 more ways to get the minute from the timestamp column in Snowflake using the date_part function as follows

-- Get the day of the minute from timestamp column using these 5 ways.

select date_part(m,current_time);
select date_part(mi,current_time);
select date_part(min,current_time);
select date_part(mins,current_time);
select date_part(minutes,current_time);

-- All will give same results only.

How to get the Seconds from timestamp in the Snowflake 

To get the second from the timestamp (0-59) in Snowflake, you can use the following code expression:

-- To get the second in Snowflake using the timestamp: second (timestamp_column)

select '2021-12-15T02:39:20.123-07:00'::timestamp as tstamp,
         second (tstamp) as "second "

Result : 20

Using DATE_PART function

To get the second (0-59) in Snowflake using the date_part function, you can use the following code expression:

There are 4 more ways to get the second from the timestamp column in Snowflake using the date_part function as follows

-- Get the day of the second from timestamp column using these 4 ways.

select date_part(s,current_time);
select date_part(sec,current_time);
select date_part(secs,current_time);
select date_part(seconds,current_time);

How to get the nanosecond of time in the Snowflake 

To get the nanosecond of time in Snowflake, you can use the following code expression:

Using DATE_PART function

There is one way to get the nanosecond of time using the date_part function as follows.

-- Get the nanosecond from the timestamp column using this way

select '2013-05-08T02:39:20.123-07:00'::timestamp as tstamp,date_part(ns, tstamp) ;
select '2013-05-08T02:39:20.123-07:00'::timestamp as tstamp, date_part(nsec, tstamp) ;
select '2013-05-08T02:39:20.123-07:00'::timestamp as tstamp,date_part(nanosec, tstamp) ;

select '2013-05-08T02:39:20.123-07:00'::timestamp as tstamp,date_part(nsecond, tstamp) ;

select '2013-05-08T02:39:20.123-07:00'::timestamp as tstamp,date_part(nanosecs, tstamp) ;

select '2013-05-08T02:39:20.123-07:00'::timestamp as tstamp,date_part(nseconds, tstamp) ;
select '2013-05-08T02:39:20.123-07:00'::timestamp as tstamp,date_part(nanoseconds, tstamp) ;

-- All will give same nanosecond result

How to get the epoch second of time in the Snowflake 

To get the epoch second of time in Snowflake, you can use the following code expression:

Using DATE_PART function

There is one way to get the epoch second of time using the date_part function as follows.

-- Get the epoch second from the timestamp column using this way

select  date_part(epoch, current_time) ;
select  date_part(epoch_seconds, current_time) ;


-- All will give same nanosecond result

How to convert the String into Date in Snowflake 

User function To_Date(String, Format)

Example : To_Date(‘2021-12-25’, ‘YYYY-MM-DD’)

Whenever you want to convert the string into the date you can use the To_Date function along with the variety of the format for conversion.

For example

-- This will convert the string from YYYY-MM-DD format to the date 

select to_date('2021-12-15', 'YYYY-MM-DD')

How to handle date conversion error or exception in Snowflake

There is some time situation when you are not sure of the column value. It can convert to right date format or may be not. Or sometimes we may have bad date in the string column which is not allowing our query to the date conversion.

We can handle the date conversion error or exception in case of invalid date in much better way using the TRY_To_Date function.

TRY_To_Date function try to convert the string into date, in case it is not able to do so. Instead of throwing the exception it will return the null. Hence you query will still work although get replace by null result value.

For example:

-- This will return the NULL instead of throwing the date conversion exception
select try_to_date('2021-15-15')

How to convert the String into TimeStamp in Snowflake 

User function To_TIMEZONE_NTZ(String, Format) or To_TIMEZONE_LTZ(String, Format) or To_TIMEZONE_TZ(String, Format)

Example : To_TIMEZONE_NTZ(’11/11/2021 01:02:03′, ‘mm/dd/yyyy hh24:mi:ss’)

Whenever you want to convert the string into the timestamp you can use the To_Date function along with the variety of the format for conversion.

For example

-- This will convert the string from YYYY-MM-DD format to the date 

select to_timestamp_tz('11/11/2021 01:02:03', 'mm/dd/yyyy hh24:mi:ss');

How to handle timestamp conversion error or exception in Snowflake

There is some time situation when you are not sure of the column value. It can convert to right timestamp format or may be not. Or sometimes we may have bad timestamp in the string column which is not allowing our query to the timestamp conversion.

We can handle the timestamp conversion error or exception in case of invalid date in much better way using the TRY_To_TIMESTAMP function.

TRY_To_TimeStamp function try to convert the string into timestamp, in case it is not able to do so. Instead of throwing the exception it will return the null. Hence you query will still work although get replace by null result value.

For example:

-- This will return the NULL instead of throwing the timestamp conversion exception
select try_to_timestamp('2021-15-15')

How to convert the TimeStamp from One TimeZone to Other TimeZone in Snowflake 

User function CONVERT_TIEMZONE(String, Format)

Example : To_TIMEZONE_NTZ(’11/11/2021 01:02:03′, ‘mm/dd/yyyy hh24:mi:ss’)

Whenever you want to convert the timezone you can use the convert_timezone function available in the snowflake.

For example

-- This will convert the time from the America/Los_Angeles to Australia/Sydney Timezone


select convert_timezone('America/Los_Angeles','Australia/Sydney', '2021-12-11 15:20:00'::timestamp_ntz)

Snowflake official Documentation Link

Final Thoughts

By this, we have reached the last section of the article. In this article, we have learned how we can convert the date time in the snowflake into multiple formats. You can refer to the table to format the date time as per the need. Hope you have found this article insightful and learned the new concept of date conversion and date parts extraction in the Snowflake cloud data warehouse .

Please share your comments suggestions and feedbacks in the comment section below.

• For Azure Study material Join Telegram group : Telegram group link:
• Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
• Azure Tutorial Videos: Videos Link

Deepak Goyal

Deepak Goyal is certified Azure Cloud Solution Architect. He is having around decade and half experience in designing, developing and managing enterprise cloud solutions. He is also Big data certified professional and passionate cloud advocate.