How to calculate dates and times in Snowflake with DATEADD

If you're working with dates in Snowflake, the `DATEADD` function is your go-to for performing quick date calculations. Or, at least, it should be.

Whether you're trying to calculate a future date, look at data over a specific period of time, or compare data to a previous period, `DATEADD` helps you easily add or subtract time from dates. Let’s dive into how this function works, with some examples and practical use cases for when to use it.

What is the DATEADD function?

The `DATEADD` function in Snowflake allows you to add a specific time interval—like days, months, or years—to a given date, timestamp, or time. It’s perfect for date calculations, as it lets you manipulate date values dynamically in your queries.

Syntax of DATEADD

Here’s the basic syntax of a `DATEADD` function within a query in Snowflake:

```sql

DATEADD(<date_or_time_part>, <integer_value>, <date_or_time_expression>)

```

Within that, you’ll notice a few different parameters:

• date_or_time_part: Specifies which part of the date you want to change—like `day`, `month`, `year`, `hour`, etc.
• integer_value: Reflects the number of units to add (or subtract) from your date.
• date_or_time_expression: The date, time, or timestamp value you want to manipulate.

So, let’s say you work at a SaaS company that has an average sales cycle of 90 days after someone becomes a lead. If you were managing sales data for your pipeline and wanted to project a date you’re hoping to convert that lead into a customer, you could calculate 90 days after the date a lead was created using this query:

```sql

SELECT DATEADD(day, 90, '2024-11-01') AS future_date;

```

This query will return `2025-01-30`, which is 90 days after November 1, 2024.

Examples of when to use the DATEADD function

Now you know the basics of how `DATEADD` works, but when will you get the most use out of it? Well, here are some practical, common scenarios where you might need it to bend time.

1. Calculating expiration dates

When software went from something you bought once to something you subscribed to, dates became ever important.

The end of a 30-day free trial, the date a subscription auto-renews, or when a SaaS contract ends are all pivotal pieces of data—and ones you can calculate using `DATEADD`.

Let’s run with that first example and say that your service has a 30-day free trial period. You can use `DATEADD` to automatically calculate the end date of the trial based on the signup date.

```sql

SELECT customer_id, DATEADD(day, 30, signup_date) AS trial_end_date

FROM subscriptions;

```

For promotions, if a customer signed up during a holiday sale with a 15-day promotional period, you can calculate when their promotional pricing expires in the same way.

```sql

SELECT customer_id, DATEADD(day, 15, signup_date) AS promotion_end_date

FROM subscriptions;

```

2. Creating time-based reports

`DATEADD` is an extremely useful function when creating reports, as they’re always displaying data over a period of time. Using `DATEADD`, you can automate date-based filtering for these reports without manually updating date ranges each time. 

Here’s how to create a sales report for the last 7 days:

```sql

SELECT *

FROM sales

WHERE sale_date >= DATEADD(day, -7, CURRENT_DATE);

```

Or maybe you’re building a marketing dashboard to review campaign performance over the last 30 days:

```sql

SELECT campaign_id, SUM(conversions) AS total_conversions

FROM campaign_data

WHERE click_date >= DATEADD(day, -30, CURRENT_DATE)

GROUP BY campaign_id;

```

Using `DATEADD` makes building these reports a lot easier and more scalable.

3. Comparing year-over-year and month-over-month data

Looking at data over a specific period of time is useful, but so is comparing it to a different period of time—like your year-over-year (YoY) or month-over-month (MoM) reporting.

Using `DATEADD`, you can easily look at data from the same period last year to analyze trends. For example,  you could use this query to compare this month’s sales to the same month last year:

```sql

SELECT *

FROM sales

WHERE sale_date BETWEEN DATEADD(year, -1, start_of_current_month) AND DATEADD(year, -1, end_of_current_month);

```

You could also use `DATEADD` to compare dates by month, quarter, semester—whatever chunk of time is relevant to your organization.

4. Automating customer engagement and follow-up timelines

Timeliness is everything when it comes to engaging and following up with your leads and customers. `DATEADD` can make sure you get the right data so that your team can communicate with leads at the right time.

If you want to send a follow-up email seven days after a purchase, for example, you can calculate this follow-up date in your query.

```sql

SELECT customer_id, DATEADD(day, 7, purchase_date) AS follow_up_date

FROM customer_purchases;

```

Or, maybe you want to calculate 30 days after a customer subscribes to your product so that you can send a check-in survey to see how onboarding is going.

```sql

SELECT customer_id, DATEADD(day, 30, signup_date) AS follow_up_date

FROM customer_signups;

```

Tips and tricks when using DATEADD

You should have a pretty strong grasp on how the `DATEADD` function works, but there are a handful of best practices to keep in mind.

• Use negative values to subtract: A negative value for `integer_value` will subtract time instead of adding it, so you don’t need to use separate functions for addition and subtraction.
• Compatible with various date/time parts: Though `day` might be the most common unit, `DATEADD` works with other date/time units too, like `week`, `month`, `year`, `hour`, making it adaptable for different date manipulations.
• Accuracy across leap years and month ends: `DATEADD` handles variations in month lengths and leap years, so if you add or subtract months, Snowflake adjusts the result intelligently. For example, adding one month to January 31 will return February 28 or 29, depending on the year).

Warp time with DATEADD in Snowflake

The `DATEADD` function is a powerful, flexible tool for anyone working with dates and times in Snowflake. Whether you’re building time-based reports or calculating dates for a pivotal data point, `DATEADD` does it dynamically, saving you from manually updating them each time.

Give it a try and see how it can streamline your workflows.