By the end of this article, you have learned some frequently used functions used in Snowflake when dealing with Date and Time.
To get the current date
SELECT CURRENT_DATE AS “CURRENT DATE”;
Output:
To get a current timestamp
SELECT CURRENT_TIMESTAMP AS “TIME STAMP”;
Output:
Use of DATE() or TO_DATE()
SELECT DATE(‘2022-11-21’) AS “STRING 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).
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.Get the date of the previous month from a given date
SELECT DATE(‘2022-11-21’) – INTERVAL ‘1 YEAR’ AS “DATE OF PREVIOUS YEAR FROM GIVEN MONTH”;
Output:
Extract MONTH, DAY, YEAR, etc from a date
SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS “CURRENT YEAR”, EXTRACT(MONTH FROM CURRENT_DATE) AS “CURRENT MONTH”, EXTRACT(DAY FROM CURRENT_DATE) AS “CURRENT 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.
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.
Finally some important Date formats
Get the Date format in DD-MM-YY
SELECT TO_CHAR(DATE(‘2022-11-21’), ‘DD-MM-YY’) AS DATE_DD_MM_YY;
Output:
Get the Date format in MM-YYYY
SELECT TO_CHAR(DATE(‘2022-11-21’), ‘MM-YYYY’) AS MM_YYYY;
Output:
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:
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!