Explained: DATEADD() function in Snowflake ?

Are you looking to find how to use the DATEADD() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the DATEADD 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 what and how to use DATEADD() function. I will explain the DATEADD() function by taking a practical example. So don’t waste time, let’s start a step-by-step guide to understanding what is the DATEADD() expression.

What is DATEADD() Function?

DATEADD() helps to add a specific value for the date or time part. For example,If I want to get a specific date after 150 days from the present date then we can use this function, which will get the exact output date.

How can we use DATEADD() Function ?

DATEADD() function is used to add the specified value for the specified date or time part to a date, time, or timestamp.

  • The units are used is a Date part ( year, month, date ) or Time part (hours, minute, second) only relevant parts will be used.
    • Example :
  • Hour uses only the hour from the time/timestamp.
    • Minute uses the hour and minutes from the time/timestamp.
    • Second uses the entire time/timestamp.
    • Year uses only the year from the date.
    • Month uses the month and year from the date.
    • Day uses the entire date.

What is the syntax of the DATEADD() function in Snowflake?

-- Syntax : 

DATEADD ( Date_or_time_part, Value, Expression)

DATEADD() Argument Details :

Date_or_time_partThe unit value ( Mandatory )
ValueNumber ( Mandatory )
ExpressionDate or Time Expression ( Mandatory )
Table 1: DateAdd () function in Snowflake Argument List

Date_or_time_part

This argument indicates the units of time that we want to add. Let us say if you want to add 2 days, then the unit will be the day.

Value

This is the number of units that you wanted to add. For example, you want to add two months, then it will be two.

Expression

Expression evaluates to a date, time, or timestamp. This is the main source to which we will add the day, month, or time.

Returns :

  • If Expression is Time, then it returns time data type.
  • If Expression is Timestamp, then it returns timestamp data type.
  • If Expression is Date, then it returns Date data type.

Examples of DateAdd() in Snowflake:

select to_date('2014-05-08') as v1, dateadd(year, 3, to_date('2014-05-08')) as v;

+------------+------------+
| V1         | V          |
|------------+------------|
| 2014-05-08 | 2017-05-08 |
+------------+------------+



select dateadd(month, 1, '2002-01-31'::date) as different_day;

+---------------+
| DIFFERENT_DAY |
|---------------|
| 2002-02-28    |
+---------------+

When you should use DATEADD() Function in Snowflake?

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

  • If you want to add days, months, or years to a specific date, then we can make use of this function. For example, if we want to add 5 days to 5th january then the resultant output will be 10th january.

Real World Use Case Scenarios for DATEADD Function in Snowflake

  • If you want to extend the time for bill payment.
  • If we want to know the exact date from today that occurs after 125 days.

DATEADD() Snowflake Official Documentation Link

Final Thoughts

In this article, we have learned about DATEADD() 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.

Leave a Comment