FIRST_DAY_OF()

All functions > DATE AND TIME > FIRST_DAY_OF()

Returns the first calendar day of the period containing the given date or timestamp, as midnight on that day.

Signatures

Returns: Midnight TIMESTAMP on the first day of the period

FIRST_DAY_OF(date: DATE | TIMESTAMP, unit: VARCHAR) → TIMESTAMP

Notes

• unit must be one of: WEEK, MONTH, QUARTER, HALF, YEAR (recognized case-insensitively after trimming whitespace)
• Week uses the same week boundaries as DATE_TRUNC(..., 'week') (Monday–Sunday)
• Half means January–June vs July–December (first day is January 1 or July 1)
• Non-literal unit values are evaluated at runtime; unknown values surface as an execution error
• Returns NULL if date or unit is NULL

Examples

SELECT
    f1 := FIRST_DAY_OF(TIMESTAMP '2024-03-20 15:00:00', 'MONTH'), -- Start of March
    f2 := FIRST_DAY_OF(TIMESTAMP '2024-03-20 12:00:00', 'WEEK'), -- ISO week containing 20 Mar 2024 starts Monday
    f3 := FIRST_DAY_OF(TIMESTAMP '2024-02-10 00:00:00', 'QUARTER'), -- Start of Q1
    f4 := FIRST_DAY_OF(TIMESTAMP '2024-05-01 00:00:00', 'HALF'), -- Start of first half-year
    f5 := FIRST_DAY_OF(TIMESTAMP '2024-09-01 00:00:00', 'HALF'), -- Start of second half-year
    f6 := FIRST_DAY_OF(TIMESTAMP '2024-07-04 00:00:00', 'YEAR') -- Start of calendar year
;