Christianlauer

Summary

The provided web content serves as a concise cheat sheet for working with date and time functions in Snowflake SQL, highlighting common operations such as date conversion, composition, difference calculation, and truncation.

Abstract

The article "Snowflake Date and Time Cheat Sheet" offers a practical guide for individuals frequently working with Snowflake SQL, particularly focusing on date and time manipulation. It outlines five essential functions: TO_DATE for string-to-date conversion, DATE_FROM_PARTS for constructing dates from individual components, DATEDIFF for calculating the interval between two dates or times, EXTRACT or DATE_PART for retrieving specific parts of a timestamp, and DATE_TRUNC for rounding down timestamps to specified units. These functions are commonly used in data engineering and analysis tasks, and the article provides examples and images to illustrate their application. The author also encourages

Snowflake Date and Time Cheat Sheet

How to work with Datetimes in Snowflake SQL

The following article can be a small cheat sheet for you, if you work a lot with Snowflake or are just learning. The following functions are those, which in my opinion, can be used relatively often.

Function1: To Date

One of the most frequently used functions by me and many others is probably TO_DATEor DATEto convert e.g. a STRING into a date.

SELECT TO_DATE(‘2022–10–17’)

Convert a String to a Date — Image by Author

A function that is probably often used by Data Engineers, e.g. when other data types have to be converted from source systems. To prevent errors, you can use the TRY_TO_DATE function which performs the same operation but with error-handling support [1]. The same applies to TO_TIME or TIME, which does not convert a date but a time.

Function 2: Date from Parts

With the function DATE_FROM_PARTSyou can compose a date from single values, which then also has the data type.

Select DATE_FROM_PARTS(2000,2,1)

Function 3: Date Difference

A function that could be interesting for Data Analysts and Data Scientists is the DATEDIFF function. With this you can calculate the difference between two dates or times. Certainly interesting in data analytics, I just recently used this on payment data for example.

SELECT DATEDIFF(
 DAYS, ‘2022–10–10 15:23:00’::TIMESTAMP, 
 ‘2022–05–10 23:39:00’::TIMESTAMP) 
AS diff_in_years;

In this example, I calculated the difference of two TIMESTAMP in DAYS.

Result of Date Difference — Image by Author

Function 4: Extract or Date Part

Here, Snowflake offers two functions — for whatever reason, just choose one. Here, you can e.g. extract a certain part from a timestamp, in the example below the year was chosen.

SELECT EXTRACT(DAY FROM TO_TIMESTAMP(‘2022–05–10T23:39:20’))

Difference in days between two Timestamps — Image by Author

Function 5: Date Trunc

The DATE_TRUNC is a similar use case but is not the same as extraction. That means that truncating a timestamp down to the quarter returns the timestamp corresponding to midnight of the first day of the quarter for the input timestamp [2].

SELECT TO_DATE (‘2022–10–08T23:39:20’) AS “DATE1”,
 DATE_TRUNC(‘YEAR’, “DATE1”) AS “TRUNCATED TO YEAR”,
 DATE_TRUNC(‘MONTH’, “DATE1”) AS “TRUNCATED TO MONTH”,
 DATE_TRUNC(‘DAY’, “DATE1”) AS “TRUNCATED TO DAY”;

Summary

These were my most used Date and Time functions in Snowflake SQL. I use them quite often in the area of Data Engineering as well as Data Analysis. For many more functions, just take a look at the official Snowflake documentation, if I forgot a very important function, feel free to give me feedback!

Possibly also interesting articles for you are: