Working with Date and Time in Snowflake

Working with Date and Time in Snowflake

By the end of this article, you have learned some frequently used functions used in Snowflake when dealing with Date and Time.

  1. To get the current date

     SELECT CURRENT_DATE ASCURRENT DATE”;
    

    Output:

  2. To get a current timestamp

     SELECT CURRENT_TIMESTAMP ASTIME STAMP”;
    

    Output:

  3. Use of DATE() or TO_DATE()

     SELECT DATE(‘2022-11-21’) ASSTRING IN DATE DATA TYPE”;
    

    Output:

    Note #1. The above function will convert the string expression to date data type.

    Note #2. I suggest using TRY_TO_DATE() function which performs the same task but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).

    Note #3. Remember the format in which the date is entered in the above function(e.g. YYYY-MM-DD).

  4. Use of DATE_TRUNC()

     SELECT DATE_TRUNC(‘YEAR’, DATE(‘2022-11-21’)) AS “TRUNCATED TO YEAR”;
    

    Output:

    Note #1. Truncation does not remove the month and day instead, it sets them to the earliest date in the specified period. For example,

    DATE_TRUNC('QUARTER', TO_DATE('2013-08-05')) returns the first date in the third quarter of the year 2013, which is July 1, 2013.

  5. Get the date of the previous month from a given date

     SELECT DATE(‘2022-11-21’) – INTERVAL1 YEARASDATE OF PREVIOUS YEAR FROM GIVEN MONTH”;
    

    Output:

  6. Extract MONTH, DAY, YEAR, etc from a date

     SELECT EXTRACT(YEAR FROM CURRENT_DATE) ASCURRENT YEAR”, EXTRACT(MONTH FROM CURRENT_DATE) ASCURRENT MONTH”, EXTRACT(DAY FROM CURRENT_DATE) ASCURRENT DAY”;
    

    Output:

    Note #1. From the above output, you can see that the EXTRACT function returns YEAR, MONTH, and DAY from CURRENT_DATE(at the time of writing this article it was 2023-01-05) when we use appropriate values.

  7. Evaluating the difference between dates

     SELECT DATEDIFF(‘MONTH’, '2022-11-21', '2023-01-05') AS DATE_DIFF;
    

    Output:

    Note #1. The function returns the result of subtracting the second argument from the third argument.

  8. Finally some important Date formats

    1. Get the Date format in DD-MM-YY

       SELECT TO_CHAR(DATE(‘2022-11-21’), ‘DD-MM-YY’) AS DATE_DD_MM_YY;
      

      Output:

    2. Get the Date format in MM-YYYY

       SELECT TO_CHAR(DATE(‘2022-11-21’), ‘MM-YYYY’) AS MM_YYYY;
      

      Output:

    3. Get the Date format in MON-YYYY (i.e. month name and year)

       SELECT TO_CHAR(DATE(‘2022-11-21’), ‘MON-YYYY’) AS MON_YYYY;
      

      Output:

    4. Get a three-letter day-of-week name format

       SELECT TO_CHAR(TO_DATE(‘1993-08-17’), ‘DY’) AS DAY_NAME;
      

      Output:

Conclusion

You can use different values in the above functions to get the required results. For example, using DATE_TRUNC() to truncate values till a month or finding the difference of times( and not just dates) or returning the date a few months before a given date. Also, there are single functions that can save you time writing big queries(like DAYNAME(), LAST_DAY(), etc).

There is one thing I would like to point out make sure the column type is DATE. Let me know your thoughts in the comment section about the article or what's one thing you learn.

If you keep practising this then you will remember those functions.

Happy Learning!