First and Last Day in the Week, Month, Quarter or Year Functions
Use the following functions to find the first and last week, month, quarter, or year for both standard and fiscal calendars. These functions
accept DateTime, DateOnly, and legacy input values. They return the same type as the input value.
Date position functions take this syntax.
SELECT functionName(Date) AS DateAlias FROM dataset;
week_first_day(date)
Accepts a DateTime, DateOnly, or legacy Date object as input. Returns an object of the same type that corresponds to the
first day (Sunday) of the week that contains the specified date.
fiscal_week_first_day(date)
Accepts a DateTime, DateOnly, or legacy Date object as input. Returns an object of the same type that corresponds to the
first day (Monday) of the fiscal week that contains the specified date.
month_first_day(date)
Accepts a DateTime, DateOnly, or legacy object as input. Returns an object of the same type that corresponds to the first day
of the month that contains the specified date.
fiscal_month_first_day(date)
Accepts a DateTime, DateOnly, or legacy Date object as input. Returns an object of the same type that corresponds to the
first day of the fiscal month that contains the specified date.
quarter_first_day(date)
Accepts a DateTime, DateOnly, or legacy Date object as input. Returns an object of the same type that corresponds to the
first day of the quarter that contains the specified date.
fiscal_quarter_first_day(date)
Accepts a DateTime, DateOnly, or legacy Date object as input. Returns an object of the same type that corresponds to the
first day of the fiscal quarter that contains the specified date. By default, the first fiscal quarter is defined as February, March, April.
The second quarter is May, June, July. The third is August, September, October. The fourth is November, December, January.
year_first_day(date)
Accepts a DateTime, DateOnly, or legacy Date object as input. Returns an object of the same type that corresponds to the
first day of the year that contains the specified date.
fiscal_year_first_day(date)
Accepts a DateTime, DateOnly, or legacy Date object as input. Returns an object of the same type that corresponds to the
first day of the fiscal year that contains the specified date. By default, the fiscal year begins on February 1.
week_last_day(date)
Accepts a DateTime, DateOnly, or legacy Date object as input. Returns an object of the same type that corresponds to the
last day (Saturday) of the week that contains the specified date.
fiscal_week_last_day(date)
Accepts a DateTime, DateOnly, or legacy Date object as input. Returns an object of the same type that corresponds to the
last day (Sunday) of the fiscal week that contains the specified date.
month_last_day(date)
Accepts a DateTime, DateOnly, or legacy Date object as input. Returns an object of the same type that corresponds to the
last day of the month that contains the specified date.
48
First and Last Day in the Week, Month, Quarter or Year
Functions
Date Functions