Snowflake adds support for INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.

• From: Saqib | Date: Fri, 27 Mar 2026 04:54:52 +0000 #
  Subject: Snowflake adds support for INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.

  Snowflake now support INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. e.g. SELECT TO_DATE('2024-01-01') + INTERVAL '-1-1' YEAR TO MONTH AS dateplusoneyearone_month; +------------------------------+

  DATEPLUSONEYEARONE_MONTH
  2022-12-01

  +------------------------------+ Add a period of time to a timestamp: SELECT TO_TIMESTAMP('2024-01-01 08:08:08.99') + INTERVAL '1 01:01:01.7878' DAY TO SECOND AS dateplusperiodoftime; +--------------------------+

  DATEPLUSPERIODOFTIME
  2024-01-02 09:09:10.777

  +--------------------------+

  [ Reply ]
  • |-- Reply
    From: Saqib | Date: Sat, 28 Mar 2026 05:28:33 +0000 #

    a nice blogpost on how to use the INTERVAL data types in Snowflake: https://medium.com/snowflake/snowflake-supports-native-interval-data-type-public-preview-0a7a03dbf089 Some examples from the blog post: We start by defining a subscriptions table with an INTERVAL YEAR TO MONTH column: CREATE OR REPLACE TABLE subscriptions ( customer_id INT, start_date DATE, plan_duration INTERVAL YEAR TO MONTH, promo_extension INTERVAL MONTH(2), payment_terms INTERVAL DAY(3) ); This lets us model "6-month plan" as INTERVAL '0-6' YEAR TO MONTH, and "1-year plan" as INTERVAL '1-0' YEAR TO MONTH. Inserting Subscription Plans (DML) INSERT INTO subscriptions VALUES (101, '2024-01-15', '0-6', '1', '30'), (102, '2023-07-01', '1-0', '6', '45'), (103, '2022-12-01', '1-6', '0', '30'); To calculate each customer's renewal date: -- Renewal date with promotion -- Invoice due date after renewal SELECT customer_id, start_date, planduration::VARCHAR AS planlen, promoextension::VARCHAR AS promoext, paymentterms::VARCHAR AS netterms, -- Base renewal startdate + planduration AS renewal_date, -- Renewal with promo (adds months cleanly) startdate + planduration + promoextension AS renewalwith_promo, -- Assume the renewal invoice is issued on the renewalwithpromo date; -- payment is due after the stored NET terms (in days). startdate + planduration + promo_extension

    • paymentterms AS invoicedue

    FROM subscriptions ORDER BY customer_id; Because plan_duration is a real INTERVAL, Snowflake will correctly compute: 6 months after Jan 15, 2024 -> July 15, 2024 1 year after July 1, 2023 -> July 1, 2024 18 months after Dec 1, 2022 -> June 1, 2024

    [ Reply ]