Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

How to Use Date Functions in Snowflake: Syntax and Examples

In Snowflake, you use DATE functions like CURRENT_DATE(), DATEADD(), and DATEDIFF() to work with dates. These functions help you get the current date, add or subtract time, and calculate differences between dates easily.
📐

Syntax

Snowflake provides several date functions with simple syntax:

  • CURRENT_DATE(): Returns today's date.
  • DATEADD(unit, value, date): Adds or subtracts a time unit (like day, month) to a date.
  • DATEDIFF(unit, start_date, end_date): Calculates the difference between two dates in the specified unit.
sql
SELECT CURRENT_DATE() AS today_date;
SELECT DATEADD(day, 5, '2024-06-01') AS new_date;
SELECT DATEDIFF(day, '2024-06-01', '2024-06-10') AS days_diff;
Output
TODAY_DATE 2024-06-15 NEW_DATE 2024-06-06 DAYS_DIFF 9
💻

Example

This example shows how to get the current date, add 10 days, and find the difference in days between two dates.

sql
SELECT
  CURRENT_DATE() AS current_date,
  DATEADD(day, 10, CURRENT_DATE()) AS date_plus_10,
  DATEDIFF(day, '2024-06-01', CURRENT_DATE()) AS days_since_june_1;
Output
CURRENT_DATE | DATE_PLUS_10 | DAYS_SINCE_JUNE_1 2024-06-15 | 2024-06-25 | 14
⚠️

Common Pitfalls

Common mistakes when using date functions in Snowflake include:

  • Using string dates without quotes or in wrong format (Snowflake expects 'YYYY-MM-DD').
  • Mixing up the order of arguments in DATEDIFF() (start date first, end date second).
  • Forgetting that DATEADD() can subtract by using negative values.
sql
/* Wrong: missing quotes around date string */
-- SELECT DATEADD(day, 5, 20240601);

/* Right: use quotes and correct format */
SELECT DATEADD(day, 5, '2024-06-01');

/* Wrong: swapped dates in DATEDIFF */
-- SELECT DATEDIFF(day, '2024-06-10', '2024-06-01');

/* Right: start date first, end date second */
SELECT DATEDIFF(day, '2024-06-01', '2024-06-10');
Output
NEW_DATE 2024-06-06 DAYS_DIFF 9
📊

Quick Reference

FunctionPurposeExample
CURRENT_DATE()Returns today's dateSELECT CURRENT_DATE();
DATEADD(unit, value, date)Adds or subtracts timeSELECT DATEADD(day, 3, '2024-06-01');
DATEDIFF(unit, start_date, end_date)Calculates difference between datesSELECT DATEDIFF(day, '2024-06-01', '2024-06-10');
DATE_TRUNC(unit, date)Truncates date to unit (e.g., month)SELECT DATE_TRUNC(month, '2024-06-15');
✅

Key Takeaways

Use CURRENT_DATE() to get today's date in Snowflake.
Add or subtract time with DATEADD() by specifying unit and value.
Calculate days or other units between dates using DATEDIFF() with correct argument order.
Always use date strings in 'YYYY-MM-DD' format and enclose them in single quotes.
Remember DATE_TRUNC() to round dates to a specific unit like month or year.