DATE_TRUNC() function in Snowflake?

Explained: DATE_TRUNC() function in Snowflake?

by

Are you looking to find how to use the DATE_TRUNC() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the DATE_TRUNC function in the Snowflake? If you are looking for any of these problem solutions then you have landed on the correct page. I will also show you whbrandon aiyuk jersey youth puma suede classic velvet sneakers in cordovan leather calfskin velvet tongue and toe cap Purchase Florida state seminars jerseys, football, and various accessories for Florida state seminars balmain carbone fragrantica basket léopard femme sbloccare oblo lavatrice ariston carhartt uk babyphone mit alexa verbinden carhartt uk brandon aiyuk jersey youth air nike sneakers meia com pompom táskafül bőr scarpe eleganti senza lacci estiti eleganti max maraat and how to use DATE_TRUNC() function. I will explain the DATE_TRUNC() function by taking a practical example. So don’t waste time let’s start step by step guide to understanding what is the DATE_TRUNC() expression.haynesplumbingllc.com bežecké tenisky jorgensenfarmsinc.com propiedadesenrepublicadominicana.com bežecké tenisky חליפות מידות גדולות נשים bežecké tenisky mindfulmusclellc.com propiedadesenrepublicadominicana.com bogner overal חליפות מידות גדולות נשים janwoodharrisart.com חליפות מידות גדולות נשים holroydtileandstone.com mindfulmusclellc.com

What is DATE_TRUNC() Function ?

DATE_TRUNC() function helps to truncate the given input data i.e. date, time, or timestamp. For example, If we applied truncate to hour, then from minutes it will set to 0 ( where as year, month, date and hours will remains constant ).

How can we use DATE_TRUNC() Function ?

DATE_TRUNC() function is used to truncate date, time, or a timestamp to the specified precision.

  • The truncation is not same as extraction. For example
    • Truncating a timestamp down to the quarters returns the timestamp corresponding to midnight to the first day of the quarter for the input timestamp.
    • Extracting the quarter date part from a timestamp returns the quarter number of the year in timestamp.

What is the Syntax of the DATE_TRUNC function?

-- Syntax : 

DATE_TRUNC( Date_or_time_part, Expression )

DATE_TRUNC() Argument Details :

Date_or_time_partDate part or Time part
ExpressionDate or Time

Expression:

The expression may be a date, time, or timestamp.

Date or Time part :

This must be one of the values from the supported date and time parts list. When the date or time part is a week or any other variations, the output is controlled by the WEEK_START session parameter.

Examples of DATE_TRUNC() function in Snowflake:

Firstly, Let’s take sample data to test the DATE_TRUNC function.

What is the difference between TO_DATE and DATE_TRUNC?

select to_date('2017-07-10T23:39:20.123-07:00') 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";

+------------+-------------------+--------------------+------------------+
| DATE1      | TRUNCATED TO YEAR | TRUNCATED TO MONTH | TRUNCATED TO DAY |
|------------+-------------------+--------------------+------------------|
| 2017-07-10 | 2017-01-01        | 2017-07-01         | 2017-07-10       |
+------------+-------------------+--------------------+------------------+

What is the difference between TO_TIMESTAMP and DATE_TRUNC?

select to_timestamp('2017-07-10T23:39:20.123-07:00') as "TIMESTAMP1",
       date_trunc('HOUR', "TIMESTAMP1") as "TRUNCATED TO HOUR",
       date_trunc('MINUTE', "TIMESTAMP1") as "TRUNCATED TO MINUTE",
       date_trunc('SECOND', "TIMESTAMP1") as "TRUNCATED TO SECOND";

+-------------------------+-------------------------+-------------------------+-------------------------+
| TIMESTAMP1              | TRUNCATED TO HOUR       | TRUNCATED TO MINUTE     | TRUNCATED TO SECOND     |
|-------------------------+-------------------------+-------------------------+-------------------------|
| 2017-07-10 23:39:20.123 | 2017-07-10 23:00:00.000 | 2017-07-10 23:39:00.000 | 2017-07-10 23:39:20.000 |
+-------------------------+-------------------------+-------------------------+-------------------------+

As we can see in the above example, the value of truncated to year has an only same year but month and date got set to starting (1). The value of truncated to month has only same year and month but the date got set to starting (1) and same for truncated to date i.e. having the same value as input.

When you should use DATE_TRUNC() Function in Snowflake?

There are certain use case scenarios when it is recommended to use the DATE_TRUNC function within the Snowflake cloud data warehouse which are as follows:

  • If you want reset the timestamp attributes and remains constant for few then we can make use of this function.

Real World Use Case Scenarios for DATE_TRUNC Function in Snowflake

  • Lets consider there are 50 people in a queue but, we can able to manage only first 20 people for the day and the rest will not be able to apply for the post.

DATE_TRUNC() Snowflake Official Documentation Link

Final Thoughts

In this article, we have learned about DATE_TRUNC() function and uses with the examples explained clearly. I have also covered different scenarios with a practical example that could be possible. I hope the information that was provided is helped in gaining the knowledge.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

Revanth Kumar