Date/Time Functions

The largest category, and the one where the databases agree least. KQL works with four temporal kinds — DATE, TIME, TIMESTAMP and INSTANT — described in docs/TEMPORAL.md; this page is about the functions over them.

Where the engines disagreed, the answer is pinned

Most functions here render straight through. Two do not, and they are worth knowing about:

dayofweek counts Monday as 1 and Sunday as 7, the ISO way. Left alone, the engines gave three different numbers for the same Sunday — 0, 1 and 7 — and several took the answer from a session setting (NLS_TERRITORY, DATEFIRST, WEEK_START), so the same query could differ between two connections to the same database. week is ISO-8601 for the same reason: week 1 is the one containing the first Thursday, which is why early January sometimes falls in week 52 or 53 of the previous year.

Where an engine's own answer depends on a session setting, the dialect computes it from a fixed anchor instead of asking the session.

Prefer the named boundaries to date_trunc

day_begin, week_begin, month_begin, quarter_begin, year_begin and their _end partners are the portable way to snap a value to a period. date_trunc(part, value) does the same for 'day', 'month', 'quarter' and 'year' — and only those four, because that is the set every dialect can express. Ask for 'hour' and SQLite used to answer with a blank column rather than an error; it now fails by name.

The _end functions return the last day of the period, always a date. That is deliberate: "the last instant" has no value independent of precision. For a timestamp range, filter half-open with begin and the next begin.

Distances count whole units, toward zero

days_between, months_between and years_between follow java.time's ChronoUnit: complete units only, and the remainder is dropped in the direction of the sign.

months_between('2023-01-31', '2023-03-30')  →  1    -- the second month is not complete

Durations are literals, not function calls

2d4h        two days and four hours
1y2mo       one year and two months
-2d4h       *both* components negated — 52 hours back, not 2 days back and 4 hours forward

Units are ms s min h d w mo q y, and they must be written largest-first (1y2mo15d, not 15d2mo1y); the other order is a positioned error rather than a silent reordering. A leading minus applies to the whole literal, so mixed signs cannot be written — that is the one shape the notation does not have.

Oracle is the exception worth naming: it keeps YEAR TO MONTH and DAY TO SECOND in separate types, so a duration mixing the two (1y2mo3d) has no value there. Adding it to a date works — that expands into steps — but using it as a value does not.

now, clock_now and today

now is the statement's timestamp and does not move within it. clock_now reads the wall clock at the moment of the call, so it may advance mid-statement on the engines that distinguish the two (PostgreSQL clock_timestamp(), MariaDB SYSDATE()); elsewhere the two are equal. today is the current date.

Parsing, formatting and zones

parse_date, parse_time and parse_timestamp read text with a format mask, to_char writes it. The mask is written once in KQL's vocabulary and translated per dialect — the tokens are on the Data Type Formatting Functions page. SQLite has no mask-based parsing at all and SQL Server takes culture or style codes rather than masks, so both declare the three unsupported.

at_zone and to_utc cross named time zones. SQLite has no time-zone database and declares both unsupported; the other seven support them.

now

now() → TIMESTAMP

Statement timestamp — fixed for the whole statement (PostgreSQL statement_timestamp(), otherwise CURRENT_TIMESTAMP).

Sample query:

// now: the current timestamp.
FIND orders o
FETCH now() current_ts

Generated SQL

duckdb · oracle · snowflake · mssql · mariadb · sqlite · trino

-- now: the current timestamp.
SELECT
  CURRENT_TIMESTAMP AS current_ts
FROM
 orders o

The remaining dialects differ only in this expression:

clock_now

clock_now() → TIMESTAMP

Wall-clock timestamp read at the moment of the call. Unlike now, it may advance within a single statement on dialects that distinguish the two (PostgreSQL clock_timestamp(), MySQL/MariaDB SYSDATE()); elsewhere it equals now.

Sample query:

// clock_now: the current wall-clock timestamp.
FIND orders o
FETCH clock_now() wall_clock_ts

Generated SQL

duckdb · oracle · snowflake · mssql · sqlite · trino

-- clock_now: the current wall-clock timestamp.
SELECT
  CURRENT_TIMESTAMP AS wall_clock_ts
FROM
 orders o

The remaining dialects differ only in this expression:

today

today() → DATE

Current date.

Sample query:

// today: the current date.
FIND orders o
FETCH today() todays_date

Generated SQL

oracle · snowflake · postgresql · mariadb · sqlite · trino

-- today: the current date.
SELECT
  CURRENT_DATE AS todays_date
FROM
 orders o

The remaining dialects differ only in this expression:

year

year(value: date/time) → INTEGER

Extracts the year part of a date or timestamp.

Sample query:

// year: the year component of the order date.
FIND orders o
FETCH year(o.order_date) order_year

Generated SQL

duckdb · snowflake · mssql · mariadb · trino

-- year: the year component of the order date.
SELECT
  year(o.order_date) AS order_year
FROM
 orders o

The remaining dialects differ only in this expression:

month

month(value: date/time) → INTEGER

Extracts the month part of a date or timestamp.

Sample query:

// month: the month component of the order date.
FIND orders o
FETCH month(o.order_date) order_month

Generated SQL

duckdb · snowflake · mssql · mariadb · trino

-- month: the month component of the order date.
SELECT
  month(o.order_date) AS order_month
FROM
 orders o

The remaining dialects differ only in this expression:

day

day(value: date/time) → INTEGER

Extracts the day part of a date or timestamp.

Sample query:

// day: the day-of-month of the order date.
FIND orders o
FETCH day(o.order_date) order_day

Generated SQL

duckdb · snowflake · mssql · mariadb · trino

-- day: the day-of-month of the order date.
SELECT
  day(o.order_date) AS order_day
FROM
 orders o

The remaining dialects differ only in this expression:

hour

hour(value: date/time) → INTEGER

Extracts the hour part of a date or timestamp.

Sample query:

// hour: the hour component of a TIMESTAMP.
FIND check_temporal c
FETCH hour(c.timestamp_timestamp) hour_of_day

Generated SQL

duckdb · snowflake · mariadb · trino

-- hour: the hour component of a TIMESTAMP.
SELECT
  hour(c.timestamp_timestamp) AS hour_of_day
FROM
 check_temporal c

The remaining dialects differ only in this expression:

minute

minute(value: date/time) → INTEGER

Extracts the minute part of a date or timestamp.

Sample query:

// minute: the minute component of a TIMESTAMP.
FIND check_temporal c
FETCH minute(c.timestamp_timestamp) minute_of_hour

Generated SQL

duckdb · snowflake · mariadb · trino

-- minute: the minute component of a TIMESTAMP.
SELECT
  minute(c.timestamp_timestamp) AS minute_of_hour
FROM
 check_temporal c

The remaining dialects differ only in this expression:

second

second(value: date/time) → INTEGER

Extracts the second part of a date or timestamp.

Sample query:

// second: the second component of a TIMESTAMP.
FIND check_temporal c
FETCH second(c.timestamp_timestamp) second_of_minute

Generated SQL

duckdb · snowflake · mariadb · trino

-- second: the second component of a TIMESTAMP.
SELECT
  second(c.timestamp_timestamp) AS second_of_minute
FROM
 check_temporal c

The remaining dialects differ only in this expression:

quarter

quarter(value: date/time) → INTEGER

Quarter of the year, 1-4.

Sample query:

// quarter: which quarter of the year the order falls in.
FIND orders o
FETCH quarter(o.order_date) order_quarter

Generated SQL

duckdb · snowflake · mariadb · trino

-- quarter: which quarter of the year the order falls in.
SELECT
  quarter(o.order_date) AS order_quarter
FROM
 orders o

The remaining dialects differ only in this expression:

week

week(value: date/time) → INTEGER

ISO-8601 week of the year, 1-53. Week 1 is the one containing the first Thursday, so early January can fall in week 52 or 53 of the previous year.

Sample query:

// week: the ISO-8601 week number of the order date.
FIND orders o
FETCH week(o.order_date) order_week

Generated SQL

duckdb · trino

-- week: the ISO-8601 week number of the order date.
SELECT
  week(o.order_date) AS order_week
FROM
 orders o

The remaining dialects differ only in this expression:

dayofweek

dayofweek(value: date/time) → INTEGER

Day of the week the ISO way: Monday is 1 and Sunday is 7. Engines disagree wildly here — some count Sunday as 0, some as 1 — so this is normalised, and the same query gives the same number on every database.

Sample query:

// dayofweek: which weekday the order was placed on, Monday = 1.
FIND orders o
FETCH dayofweek(o.order_date) order_weekday

Generated SQL

duckdb

-- dayofweek: which weekday the order was placed on, Monday = 1.
SELECT
  isodow(o.order_date) AS order_weekday
FROM
 orders o

The remaining dialects differ only in this expression:

dayofyear

dayofyear(value: date/time) → INTEGER

Day of the year, 1-366.

Sample query:

// dayofyear: how far into the year the order date is.
FIND orders o
FETCH dayofyear(o.order_date) order_dayofyear

Generated SQL

duckdb · snowflake · mariadb

-- dayofyear: how far into the year the order date is.
SELECT
  dayofyear(o.order_date) AS order_dayofyear
FROM
 orders o

The remaining dialects differ only in this expression:

parse_date

parse_date(value: TEXT, format: TEXT) → DATE

Parses value into a date using the format mask.

The format mask is written in KQL's own vocabulary and translated per dialect, like to_char's — see the Formatting Functions page for the tokens.

Generated SQL

Unsupported: mssql, sqlite

parse_time

parse_time(value: TEXT, format: TEXT) → TIME

Parses value into a time using the format mask.

The format mask is written in KQL's own vocabulary and translated per dialect, like to_char's — see the Formatting Functions page for the tokens.

Generated SQL

Unsupported: mssql, sqlite

parse_timestamp

parse_timestamp(value: TEXT, format: TEXT) → TIMESTAMP

Parses value into a timestamp using the format mask.

The format mask is written in KQL's own vocabulary and translated per dialect, like to_char's — see the Formatting Functions page for the tokens.

Generated SQL

Unsupported: mssql, sqlite

date_trunc

date_trunc(part: TEXT, value: date/time) → argument-dependent

Truncates value to the given precision, e.g. date_trunc('month', o.order_date).

Sample query:

// date_trunc: truncate the order date to the month.
FIND orders o
FETCH date_trunc('month', o.order_date) month_start

Generated SQL

duckdb · snowflake · postgresql · trino

-- date_trunc: truncate the order date to the month.
SELECT
  date_trunc('month', o.order_date) AS month_start
FROM
 orders o

The remaining dialects differ only in this expression:

make_date

make_date(year: INTEGER, month: INTEGER, day: INTEGER) → DATE

Builds a date from year, month and day.

Sample query:

// make_date: build a DATE from year, month and day.
FIND orders o
FETCH make_date(2024, 1, 31) built

Generated SQL

duckdb · postgresql

-- make_date: build a DATE from year, month and day.
SELECT
  make_date(2024, 1, 31) AS built
FROM
 orders o

The remaining dialects differ only in this expression:

make_time

make_time(hour: INTEGER, minute: INTEGER, second: INTEGER) → TIME

Builds a time from hour, minute and second.

Sample query:

// make_time: build a TIME from hour, minute and second.
FIND orders o
FETCH make_time(14, 30, 0) built

Generated SQL

duckdb · postgresql

-- make_time: build a TIME from hour, minute and second.
SELECT
  make_time(14, 30, 0) AS built
FROM
 orders o

The remaining dialects differ only in this expression:

Unsupported: sqlite, trino

make_timestamp

make_timestamp(year: INTEGER, month: INTEGER, day: INTEGER, hour: INTEGER, minute: INTEGER, second: INTEGER) → TIMESTAMP

Builds a timestamp from its six components.

Sample query:

// make_timestamp: build a TIMESTAMP from its parts.
FIND orders o
FETCH make_timestamp(2024, 1, 31, 14, 30, 0) built

Generated SQL

duckdb · postgresql

-- make_timestamp: build a TIMESTAMP from its parts.
SELECT
  make_timestamp(2024, 1, 31, 14, 30, 0) AS built
FROM
 orders o

The remaining dialects differ only in this expression:

Unsupported: sqlite, trino

days_between

days_between(start: date/time, end: date/time) → INTEGER

Signed number of complete days from start to end — whole 24-hour periods that fit inside the interval, not midnights crossed.

For two dates the two readings coincide, which is why the difference only shows with a time of day: days_between('2023-01-01 23:00', '2023-01-02 01:00') is 0, because two hours is not a day, even though a midnight lies between them.

Measured before this was settled, the engines split five to three: DuckDB, PostgreSQL, SQL Server, Snowflake and SQLite counted the boundary, MariaDB, Oracle and Trino the elapsed day. All eight now count the elapsed day, matching ChronoUnit.DAYS.

Sample query:

// days_between: signed day distance between two dates.
FIND check_temporal c
FETCH days_between("2023-03-01", "2023-02-01") signed_minus28

Generated SQL

duckdb

-- days_between: signed day distance between two dates.
SELECT
  CAST(trunc(date_diff('second', DATE '2023-03-01', DATE '2023-02-01') / 86400.0) AS INTEGER) AS signed_minus28
FROM
 check_temporal c

The remaining dialects differ only in this expression:

months_between

months_between(start: date/time, end: date/time) → INTEGER

Signed number of whole months completed from start to end; months_between('2023-01-31', '2023-03-30') = 1 — the second month is not complete.

Sample query:

// months_between: whole completed months between two dates.
FIND check_temporal c
FETCH months_between("2023-01-31", "2023-03-31") whole_two

Generated SQL

duckdb · postgresql

-- months_between: whole completed months between two dates.
SELECT
  (CAST(EXTRACT(YEAR FROM age(DATE '2023-03-31', DATE '2023-01-31')) * 12 + EXTRACT(MONTH FROM age(DATE '2023-03-31', DATE '2023-01-31')) AS INTEGER)) AS whole_two
FROM
 check_temporal c

The remaining dialects differ only in this expression:

years_between

years_between(start: date/time, end: date/time) → INTEGER

Signed number of whole years completed from start to end.

Sample query:

// years_between: whole years from order to shipment.
FIND orders o
FETCH years_between(o.order_date, o.shipped_date) years

Generated SQL

duckdb · postgresql

-- years_between: whole years from order to shipment.
SELECT
  CAST(EXTRACT(YEAR FROM age(o.shipped_date, o.order_date)) AS INTEGER) AS years
FROM
 orders o

The remaining dialects differ only in this expression:

calendar_distance

calendar_distance(start: date/time, end: date/time) → DURATION

Calendar (civil) distance from start to end as a mixed interval (years/months/days + clock): calendar_distance('2025-01-01', '2026-05-15 12:00') = 1y4mo14d12h. Use ts − ts for a fixed elapsed span.

Sample query:

// calendar_distance: calendar span from order to shipment (NULL when not yet shipped).
FIND orders o
FETCH calendar_distance(o.order_date, o.shipped_date) span

Generated SQL

duckdb · postgresql

-- calendar_distance: calendar span from order to shipment (NULL when not yet shipped).
SELECT
  (CAST(CAST(EXTRACT(EPOCH FROM (o.order_date)) AS BIGINT) AS VARCHAR) || ';' || CAST(CAST(EXTRACT(EPOCH FROM (o.shipped_date)) AS BIGINT) AS VARCHAR)) AS span
FROM
 orders o

The remaining dialects differ only in this expression:

day_add

day_add(value: date/time, n: INTEGER) → argument-dependent

value shifted by n days; n may be any expression.

Sample query:

// day_add: seven days after the order date.
FIND orders o
FETCH day_add(o.order_date, 7) plus_week

Generated SQL

duckdb · mariadb

-- day_add: seven days after the order date.
SELECT
  (o.order_date + INTERVAL (7) DAY) AS plus_week
FROM
 orders o

The remaining dialects differ only in this expression:

month_add

month_add(value: date/time, n: INTEGER) → argument-dependent

value shifted by n months, clamped to the end of the month: month_add('2023-01-31', 1) = 2023-02-28.

Sample query:

// month_add: one month after the order date.
FIND orders o
FETCH month_add(o.order_date, 1) next_month

Generated SQL

duckdb · mariadb

-- month_add: one month after the order date.
SELECT
  (o.order_date + INTERVAL (1) MONTH) AS next_month
FROM
 orders o

The remaining dialects differ only in this expression:

year_add

year_add(value: date/time, n: INTEGER) → argument-dependent

value shifted by n years, clamped (Feb 29 + 1 year = Feb 28).

Sample query:

// year_add: one year after the order date.
FIND orders o
FETCH year_add(o.order_date, 1) next_year

Generated SQL

duckdb · mariadb

-- year_add: one year after the order date.
SELECT
  (o.order_date + INTERVAL (1) YEAR) AS next_year
FROM
 orders o

The remaining dialects differ only in this expression:

day_begin

day_begin(value: date/time) → argument-dependent

Start of the day (time becomes 00:00:00).

Sample query:

// day_begin: midnight at the start of a TIMESTAMP's day.
FIND check_temporal c
FETCH day_begin(c.timestamp_timestamp) start_of_day

Generated SQL

duckdb · snowflake · postgresql · trino

-- day_begin: midnight at the start of a TIMESTAMP's day.
SELECT
  date_trunc('day', c.timestamp_timestamp) AS start_of_day
FROM
 check_temporal c

The remaining dialects differ only in this expression:

week_begin

week_begin(value: date/time) → argument-dependent

Monday of the week (pairs with week_end). Grouping by this is the reliable way to bucket by week: it is a real date, so it sorts, and it has none of the year-boundary trouble of grouping by year and week together — 2026-12-28 is in week 1, but of 2027.

Sample query:

// week_begin: Monday of the order's week — the sortable weekly grouping key.
FIND orders o
FETCH week_begin(o.order_date) week_start

Generated SQL

duckdb · postgresql · trino

-- week_begin: Monday of the order's week — the sortable weekly grouping key.
SELECT
  date_trunc('week', o.order_date) AS week_start
FROM
 orders o

The remaining dialects differ only in this expression:

month_begin

month_begin(value: date/time) → argument-dependent

First day of the month (pairs with month_end).

Sample query:

// month_begin: first day of the order's month.
FIND orders o
FETCH month_begin(o.order_date) m_begin

Generated SQL

duckdb · snowflake · postgresql · trino

-- month_begin: first day of the order's month.
SELECT
  date_trunc('month', o.order_date) AS m_begin
FROM
 orders o

The remaining dialects differ only in this expression:

quarter_begin

quarter_begin(value: date/time) → argument-dependent

First day of the quarter.

Sample query:

// quarter_begin: first day of the order's quarter.
FIND orders o
FETCH quarter_begin(o.order_date) q_begin

Generated SQL

duckdb · snowflake · postgresql · trino

-- quarter_begin: first day of the order's quarter.
SELECT
  date_trunc('quarter', o.order_date) AS q_begin
FROM
 orders o

The remaining dialects differ only in this expression:

year_begin

year_begin(value: date/time) → argument-dependent

First day of the year.

Sample query:

// year_begin: first day of the order's year.
FIND orders o
FETCH year_begin(o.order_date) y_begin

Generated SQL

duckdb · snowflake · postgresql · trino

-- year_begin: first day of the order's year.
SELECT
  date_trunc('year', o.order_date) AS y_begin
FROM
 orders o

The remaining dialects differ only in this expression:

week_end

week_end(value: date/time) → DATE

Sunday of the week.

Sample query:

// week_end: Sunday of the order's week.
FIND orders o
FETCH week_end(o.order_date) week_close

Generated SQL

duckdb

-- week_end: Sunday of the order's week.
SELECT
  CAST(date_trunc('week', o.order_date) + INTERVAL 6 DAY AS DATE) AS week_close
FROM
 orders o

The remaining dialects differ only in this expression:

month_end

month_end(value: date/time) → DATE

Last day of the month (Excel EOMONTH): month_end('2024-02-10') = 2024-02-29.

Sample query:

// month_end: last day of the order's month.
FIND orders o
FETCH month_end(o.order_date) m_end

Generated SQL

duckdb · snowflake

-- month_end: last day of the order's month.
SELECT
  last_day(o.order_date) AS m_end
FROM
 orders o

The remaining dialects differ only in this expression:

quarter_end

quarter_end(value: date/time) → DATE

Last day of the quarter.

Sample query:

// quarter_end: last day of the order's quarter.
FIND orders o
FETCH quarter_end(o.order_date) q_end

Generated SQL

duckdb

-- quarter_end: last day of the order's quarter.
SELECT
  last_day(date_trunc('quarter', o.order_date) + INTERVAL 2 MONTH) AS q_end
FROM
 orders o

The remaining dialects differ only in this expression:

year_end

year_end(value: date/time) → DATE

Last day of the year (December 31).

Sample query:

// year_end: last day of the order's year.
FIND orders o
FETCH year_end(o.order_date) y_end

Generated SQL

duckdb

-- year_end: last day of the order's year.
SELECT
  last_day(date_trunc('year', o.order_date) + INTERVAL 11 MONTH) AS y_end
FROM
 orders o

The remaining dialects differ only in this expression:

at_zone

at_zone(value: date/time, zone: TEXT) → TIMESTAMP

Reads value as a model-zone wall-clock value and returns its wall-clock in the named zone (e.g. for day-bucketing: date(at_zone(o.ts, 'Europe/Berlin'))).

Sample query:

// at_zone: read a model-zone wall-clock value in another zone.
FIND check_temporal c
FETCH at_zone(c.ts_diff_base, 'America/New_York') in_new_york

Generated SQL

duckdb · postgresql

-- at_zone: read a model-zone wall-clock value in another zone.
SELECT
  ((c.ts_diff_base AT TIME ZONE 'UTC') AT TIME ZONE 'America/New_York') AS in_new_york
FROM
 check_temporal c

The remaining dialects differ only in this expression:

Unsupported: sqlite

to_utc

to_utc(value: date/time, zone: TEXT) → TIMESTAMP

Inverse of at_zone: reads value as a wall-clock in the named zone and returns the model-zone wall-clock value.

Sample query:

// to_utc: read a zoned wall-clock value and return it in UTC.
FIND check_temporal c
FETCH to_utc(c.ts_diff_base, 'America/New_York') as_utc

Generated SQL

duckdb · postgresql

-- to_utc: read a zoned wall-clock value and return it in UTC.
SELECT
  ((c.ts_diff_base AT TIME ZONE 'America/New_York') AT TIME ZONE 'UTC') AS as_utc
FROM
 check_temporal c

The remaining dialects differ only in this expression:

Unsupported: sqlite