Snowflake Date and Time Functions

Business questions revolve around time: sales this month, orders per week, days since the last login. Snowflake offers a rich set of date and time functions that shift dates, measure gaps, cut timestamps into periods, and convert between time zones. A small group of functions covers most daily needs.

The Calendar and Clock Comparison

A wall calendar shows days, and a wristwatch shows time of day. Snowflake keeps both ideas in separate types. DATE stores a calendar day, TIME stores a clock time, and TIMESTAMP combines the two. Three timestamp flavors handle time zones in different ways.

TIMESTAMP FLAVORS
=================

TYPE           MEANING                                  EXAMPLE USE
-------------  ---------------------------------------  --------------------
TIMESTAMP_NTZ  No time zone, a plain wall-clock value   Event logs, batch loads
TIMESTAMP_LTZ  Stored in UTC, shown in session zone     User-facing reports
TIMESTAMP_TZ   Stores the zone offset with each value   Global transactions

Getting the Current Moment

SELECT CURRENT_DATE()       AS today,
       CURRENT_TIMESTAMP()  AS right_now,
       CURRENT_TIME()       AS clock_time;

Adding and Subtracting Time

SELECT DATEADD('day', 30, CURRENT_DATE())     AS thirty_days_later,
       DATEADD('month', -1, CURRENT_DATE())   AS one_month_ago,
       DATEADD('hour', 6, CURRENT_TIMESTAMP()) AS six_hours_later;

Measuring the Gap Between Two Dates

SELECT DATEDIFF('day', order_date, ship_date) AS days_to_ship
FROM orders;

SELECT DATEDIFF('month', signup_date, CURRENT_DATE()) AS months_as_customer
FROM customers;

DATEDIFF counts the number of boundaries crossed, so the order of the arguments matters. The earlier date goes first.

Grouping by Period with DATE_TRUNC

DATE_TRUNC rounds a timestamp down to the start of a period. It turns thousands of different timestamps into a handful of group labels.

SELECT DATE_TRUNC('month', order_date) AS month_start,
       COUNT(*)                        AS orders,
       SUM(amount)                     AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;
DATE_TRUNC EXAMPLE for 2026-10-17 14:35:09
==========================================
'hour'    -> 2026-10-17 14:00:00
'day'     -> 2026-10-17 00:00:00
'week'    -> 2026-10-12 00:00:00   (week start depends on settings)
'month'   -> 2026-10-01 00:00:00
'year'    -> 2026-01-01 00:00:00

Extracting Parts

SELECT order_date,
       YEAR(order_date)       AS yr,
       MONTH(order_date)      AS mon,
       DAYNAME(order_date)    AS weekday,
       EXTRACT(quarter FROM order_date) AS qtr,
       LAST_DAY(order_date)   AS month_end
FROM orders;

Converting Text to Dates

SELECT TO_DATE('2026-10-17')                       AS d1,
       TO_DATE('17/10/2026', 'DD/MM/YYYY')         AS d2,
       TO_TIMESTAMP('2026-10-17 14:35:09')         AS ts1,
       TRY_TO_DATE('not a date')                   AS safe_result;

TRY_ versions return NULL on bad input without stopping the query. Use them on messy source data.

Working Across Time Zones

SELECT CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', event_ts_utc) AS local_time
FROM events;

SELECT CONVERT_TIMEZONE('America/New_York', 'Europe/London', TO_TIMESTAMP_NTZ('2026-10-17 09:00:00'));

Store raw event times in UTC and convert only at the reporting layer. That habit avoids daylight saving mistakes.

Building a Date Table

Reports with gaps, such as days with zero sales, need every date present. A generated series fills the calendar.

SELECT DATEADD('day', SEQ4(), '2026-01-01'::DATE) AS calendar_date
FROM TABLE(GENERATOR(ROWCOUNT => 365));

A LEFT JOIN from this calendar to the sales table shows zero for quiet days.

A Practical Scenario

A support manager wants the average number of days tickets stay open, grouped by month of opening. The analyst uses DATEDIFF between opened and closed dates, DATE_TRUNC on the opened date, and AVG across each month. Open tickets use CURRENT_DATE as the end date. The result shows a clear trend line for the monthly review.

Common Mistakes

Comparing Timestamps With Dates

A timestamp of 2026-10-17 14:35 never equals the date 2026-10-17, because the date carries midnight. Truncate the timestamp with DATE_TRUNC or cast it with ::DATE before comparing.

Reversing DATEDIFF Arguments

DATEDIFF subtracts the first date from the second. Placing the later date first returns a negative number, which confuses reports.

Ignoring Time Zones

A session time zone changes how TIMESTAMP_LTZ values display. Two analysts in different cities may see different clock times for one event. Agree on UTC for storage, and convert at the last step of a report.

Frequently Asked Questions

How do I find the first day of the week?

DATE_TRUNC with the week unit returns the start of the week according to the WEEK_START session parameter. Set the parameter once for the account so every report agrees.

How do I include today in a last 7 days report?

Filter with order_date between DATEADD('day', -6, CURRENT_DATE()) and CURRENT_DATE(). That range holds seven calendar days, including today.

Key Points

  • DATE, TIME, and TIMESTAMP types hold different parts of time.
  • DATEADD shifts values and DATEDIFF measures gaps.
  • DATE_TRUNC creates clean group labels for reports.
  • TRY_TO_DATE avoids failures on bad text.
  • CONVERT_TIMEZONE handles zone changes; store raw times in UTC.
  • GENERATOR builds a full calendar for gap-free reports.
Previous lesson
Back to course
Next lesson