10.5 Date and fiscal functions

There is no date type (4.7). Every function below reads a date as epoch milliseconds or an ISO-8601 string (2026-03-09, 2026-03, 2026-03-09T10:00:00Z, 2026-03-09 10:00:00+02:00), computes in UTC, and returns epoch milliseconds, except date_part (a number), date_format (text), fiscal_year, fiscal_month and days_in (numbers). A null or a value of another kind is an error (`date_format` expects epoch milliseconds or an ISO-8601 string, found Null).

Units: year, quarter, month, week (weeks start on Monday), day, hour, minute, second, millisecond, singular or plural.

Epoch milliseconds of today exceed 2³², so responses carry them as floats (1772323200000.0): the wire rule of 4.5. Inside a query they stay integers.

now

now() → the current time, epoch milliseconds. Not deterministic: let $now = now() gives every row the same instant.

parse_date

parse_date(t) → t as epoch milliseconds (a number is returned as it is).

parse_date("1970-01-02")   → 86400000
parse_date("2026-03")      → 1772323200000          -- 2026-03-01T00:00:00Z

date_trunc

date_trunc(t, unit) → the start of the unit holding t.

date_trunc("2026-03-09T10:00:00Z", "month")             → 2026-03-01
date_format(date_trunc("2026-03-11", "week"), "%F")     → "2026-03-09"   -- a Monday

date_add

date_add(t, n, unit) → t moved by n units (n may be negative). Adding months or years clamps the day to the end of the month.

date_format(date_add("2026-01-31", 1, "month"), "%F")   → "2026-02-28"
date_format(date_add("2026-01-31", -2, "days"), "%F")   → "2026-01-29"
date_add("2024-01-01", m, "month")

date_diff

date_diff(start, end, unit) → the number of unit boundaries crossed from start to end (negative when end is before start).

date_diff("2025-12-31", "2026-01-01", "year")     → 1
date_diff("2026-01-01", "2026-03-15", "months")   → 2
date_diff("2026-03-01", "2026-01-01", "month")    → -2

date_part

date_part(t, part) → one part of the date, as a number.

date_part("2026-03-09", "day_of_week")    → 1
date_part("2026-03-09", "week")           → 11
date_part("2026-03-09", "quarter")        → 1
date_part("2026-03-09", "day_of_year")    → 68

Errors. unknown date part "dow"; expected year, quarter, month, week, day, day_of_week, day_of_year, hour, minute, second or millisecond.

date_format

date_format(t [, pattern]) → text. Without a pattern, ISO-8601 with milliseconds (2026-03-09T10:20:30.000Z).

date_format("2026-03-09", "%Y-%m")               → "2026-03"
date_format("2026-03-09", "%j %u %V %F")         → "068 1 11 2026-03-09"
date_format("2026-03-09T10:20:30.123Z", "%T %L") → "10:20:30 123"

Fiscal years

A fiscal year is described by its closing: either the month it closes at the end of (1 to 12; 12 is the calendar year), or the date it opens on (a timestamp on the first day of a month, repeated every year). A fiscal year is named after the calendar year it closes in.

fiscal_year

fiscal_year(t, closing) → the fiscal year holding t.

fiscal_year("2026-07-01", 6)               → 2027      -- July 2026 – June 2027
fiscal_year("2026-07-01", "2019-07-01")    → 2027      -- the same year, by its opening date

fiscal_month

fiscal_month(t, closing) → the month within the fiscal year, 1 to 12.

fiscal_month("2026-07-01", 6)              → 1

fiscal_start

fiscal_start(t, closing) → the first day of the fiscal year.

date_format(fiscal_start("2026-07-01", 6), "%F")   → "2026-07-01"

fiscal_end

fiscal_end(t, closing) → the last day of the fiscal year.

date_format(fiscal_end("2026-07-01", 6), "%F")     → "2027-06-30"

month_end

month_end(t) → the last day of the month.

date_format(month_end("2024-02-10"), "%F")         → "2024-02-29"

days_in

days_in(t, unit) → the number of days in the unit holding t.

days_in("2024-02-10", "month")     → 29
days_in("2024-02-10", "quarter")   → 91
days_in("2024-02-10", "year")      → 366
days_in("2024-02-10", "week")      → 7

Date recipes

-- Month of an ISO date, as text
derive month = date_format(date, "%Y-%m")

-- The same month as epoch milliseconds
derive month = date_trunc(date, "month")

-- ISO year-week label
derive week = concat(date_format(date, "%Y"), "-W", date_format(date, "%V"))

-- Last 12 months from a parameter
let $from = date_add($to, -11, "month") | on sales | where parse_date(date) >= $from and parse_date(date) <= month_end($to)

-- Fiscal year closing in June
derive fy = fiscal_year(date, 6), fm = fiscal_month(date, 6)

-- Pro rata of a monthly amount
derive per_day = amount / days_in(date, "month")