How to subtract days from a date in Snowflake

Learn how to subtract days from a date in Snowflake using the powerful DATEADD function.

Photo: Propel

Get started with Propel today and receive $15 in monthly credits forever. At any point, upgrade to pay-as-you-go, or contact us to learn more about our volume-based discounts. Visit our pricing page for details.

Discover the power of Snowflake's date functions to manipulate dates with precision and ease

What is the DATEADD function?

Snowflake provides a range of powerful date and time functions that help you manipulate date and time data types in various ways. One such versatile function is the DATEADD function. As the name suggests, it enables you to add or subtract a specific interval to or from a given date or timestamp value.

The DATEADD function has the following syntax,

DATEADD(<interval>, <value>, <date_or_timestamp>)

where

interval is the interval you want to add or subtract, such as “DAY”, “HOUR”, “MINUTE”, “SECOND”, etc.
value is the number of intervals you want to add to or subtract from the date or timestamp.
date_or_timestamp is the date or timestamp value to be adjusted.

How to subtract days from a date

You can subtract days from a date in Snowflake using the DATEADD function. To perform subtraction, simply pass a negative value for the value parameter.

For example, if you want to subtract 7 days from a date, the syntax would be:

SELECT DATEADD(DAY, -7, CURRENT_TIMESTAMP())

Remember that the interval parameter must be in uppercase letters. Additionally, when subtracting days, ensure the value parameter is negative.

Example: TacoSoft Date Manipulation

Let's look at an example using TacoSoft, an imaginary app to manage taquerias. Suppose you have a table called orders with the following columns:

order_id: Unique identifier for each order.
order_date: The date when the order was placed.
customer_id: Unique identifier for each customer.

Your task is to retrieve a list of all orders placed three days before a specific date. In this case, let's say you want to retrieve all orders placed three days before 2023-04-14. You can use the DATEADD function to subtract days from a date in Snowflake and achieve this goal.

Here is the SQL query for this task:

SELECT
  order_id,
  order_date,
  customer_id
FROM
  orders
WHERE
  order_date = DATEADD('DAY', -3, '2023-04-14');

This query would return all the rows in the orders table where the order_date is exactly three days before 2023-04-14.

Conclusion

In this post, we explored Snowflake's DATEADD function to subtract days from a date or timestamp. By using a negative value for the value parameter, you can easily perform date subtraction. This tool can be valuable for filtering and analyzing time-based data in your Snowflake data warehouse.

Remember that Snowflake offers many other date and time functions to help you manipulate and work with date and time data types. Don't hesitate to explore the official Snowflake documentation to learn more about these functions and how to use them effectively.

Snowflake API: Comprehensive Guide to 3 Methods With Examples

This is some text inside of a div block.

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5

Heading 6

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.