--- schema_version: "1" id: bc990fa9aa6411ab3ed3f89c17abd3027c75cec6611e3efd27d2c56436599703 title: Date/Time Functions and Operators collection: developer version: current locale: en-us portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/references/data-cloud-sql-reference/scalar-func/datetime-func.md canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/datetime-func.html source_site: developer.salesforce.com content_type: documentation content_hash: 1514840c3303180161f517047d6f10a326a440ff1f94bdebb92b0b2ce88530e2 products: - Data 360 Query Guide - Data 360 Developer Documentation --- # Date/Time Functions and Operators **Applies to:** ✅ Data 360 SQL ✅ Tableau Hyper API Use date and time functions to create time-based queries against your data. To work with data and time functions, review the [Date/Time Data Types](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/datatype/datetime.md). Most of the functions and operators on this page take the `time` or `timestamp` variant, `time with time zone` or `timestamp with time zone`. The `+` and `*` operators come in commutative pairs, for example, both date \+ integer and integer \+ date. **note:** Data 360 SQL outputs interval data in the [ISO-8601 style](https://en.wikipedia.org/wiki/ISO_8601#Time_intervals). For example, `P1DT2H3M4S` corresponds to 1 day, 2 hours, 3 minutes, and 4 seconds. ## Operators Basic arithmetic operators (`+`, `-`, `*`, `/`) for dates, times, and intervals. | Operation | Return Type | Example | | :---------------------------- | :---------- | :------------------------------------------------------------------------------- | | `date + integer` | `date` | `date '2024-03-15' + 7` → `2024-03-22` | | `date + interval` | `timestamp` | `date '2024-03-15' + interval '1 day'` → `2024-03-16 00:00:00` | | `date + time` | `timestamp` | `date '2024-03-15' + time '10:00:00'` → `2024-03-15 10:00:00` | | `interval + interval` | `interval` | `interval '1 hour' + interval '30 minutes'` → `01:30:00` | | `timestamp + interval` | `timestamp` | `timestamp '2024-03-15 12:00:00' + interval '2 hours'` → `2024-03-15 14:00:00` | | `time + interval` | `time` | `time '12:00:00' + interval '1 hour'` → `13:00:00` | | `- interval` | `interval` | `- interval '1 hour'` → `-01:00:00` | | `date - date` | `integer` | `date '2024-03-22' - date '2024-03-15'` → `7` | | `date - integer` | `date` | `date '2024-03-22' - 7` → `2024-03-15` | | `date - interval` | `timestamp` | `date '2024-03-16' - interval '1 day'` → `2024-03-15 00:00:00` | | `time - time` | `interval` | `time '14:00:00' - time '12:00:00'` → `02:00:00` | | `time - interval` | `time` | `time '13:00:00' - interval '1 hour'` → `12:00:00` | | `timestamp - interval` | `timestamp` | `timestamp '2024-03-15 14:00:00' - interval '2 hours'` → `2024-03-15 12:00:00` | | `interval - interval` | `interval` | `interval '1 hour' - interval '30 minutes'` → `00:30:00` | | `timestamp - timestamp` | `interval` | `timestamp '2024-03-15 14:00:00' - timestamp '2024-03-15 12:00:00'` → `02:00:00` | | `interval * integer` | `interval` | `interval '30 minutes' * 2` → `01:00:00` | | `interval * double precision` | `interval` | `interval '1 hour' * 1.5` → `01:30:00` | | `interval / double precision` | `interval` | `interval '1 hour' / 2` → `00:30:00` | To simplify timestamp subtraction, use `EXTRACT(EPOCH FROM ...)` to convert the values into seconds and then subtract the results. This process produces the number of seconds between the two values and adjusts for the number of days in each month, time zone changes, and daylight savings time. The "`-`" operator makes the same adjustments and returns the number of days (24-hours) and hours/minutes/seconds between the values. These queries show the differences in these approaches. The samples use `timezone = 'US/Eastern'` and also includes a daylight-savings time change between the two dates. ```sql SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') - EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'); Result: 10537200 SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') - EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00')) / 60 / 60 / 24; Result: 121.958333333333 SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00'; Result: 121 days 23:00:00 ``` ## Functions | Function | Return Type | Description | Example | | :-------------------------------------------------------------------------------------- | :------------------------- | :------------------------------------------------------------------------------------------------------------------------ | :------------------------------------------------------------------------------------------------------------------------------- | | `current_date` | `date` | Return the current date. See [Current Date/Time](#current-datetime). | | | `current_time` | `time with time zone` | Return the current time of day with time zone. See [Current Date/Time](#current-datetime). | | | `current_timestamp` | `timestamp with time zone` | Return the current date and time with time zone (start of current statement). See [Current Date/Time](#current-datetime). | | | `datediff(text, timestamp, timestamp)` | `integer` | Calculate the difference between two timestamps in the specified unit. | `datediff('day', timestamp '2001-02-03', timestamp '2001-02-13')` → `10` | | `date_add(text, int, timestamp)` | `timestamp` | Add a multiple of the specified date unit to a given date or timestamp | `date_add('year', 2, timestamp '2021-02-16 11:01:02')` → `2023-02-16 11:01:02` | | `date_part(text, timestamp)` | `double precision` | Return a subfield from a timestamp (equivalent to [EXTRACT](#extract-and-date_part)). | `date_part('hour', timestamp '2001-02-16 20:38:40')` → `20` | | `date_part(text, interval)` | `double precision` | Return a subfield from an interval (equivalent to [EXTRACT](#extract-and-date_part)). | `date_part('month', interval '2 years 3 months')` → `3` | | `date_trunc(text, timestamp)` | `timestamp` | Truncate a timestamp to the specified precision. See [date_trunc](#date_trunc). | `date_trunc('hour', timestamp '2001-02-16 20:38:40')` → `2001-02-16 20:00:00` | | `date_trunc(text, interval)` | `interval` | Truncate an interval to the specified precision. See [date_trunc](#date_trunc). | `date_trunc('hour', interval '2 days 3 hours 40 minutes')` → `2 days 03:00:00` | | `day(timestamp)` | `integer` | Return the day from a timestamp (equivalent to `EXTRACT(DAY FROM ...)`). | `day(timestamp '2001-02-16 20:38:40')` → `16` | | `day(interval)` | `integer` | Return the day from an interval (equivalent to `EXTRACT(DAY FROM ...)`). | `day(interval '42 days')` → `42` | | `extract(field from timestamp)` | various data types | Return a subfield from a timestamp. See [EXTRACT](#extract-and-date_part). | `extract (hour from timestamp '2001-02-16 20:38:40')` → `20` | | `extract(field from interval)` | various data types | Return a subfield from an interval. See [EXTRACT](#extract-and-date_part). | `extract(month from interval '2 years3 months')` → `3` | | `hour(timestamp)` | `integer` | Return the hour from a timestamp (equivalent to `EXTRACT(HOUR FROM ...)`). | `hour(timestamp '2001-02-16 20:38:40')` → `20` | | `hour(interval)` | `integer` | Return the hour from an interval (equivalent to `EXTRACT(HOUR FROM ...)`). | `hour(interval '121 minutes')` → `2` | | `justify_days(interval)` | `interval` | Adjust an interval so 30-day time periods are represented as months. | `justify_days(interval '35 days')` → `1 mon 5 days` | | `justify_hours(interval)` | `interval` | Adjust an interval so 24-hour time periods are represented as days. | `justify_hours (interval '27 hours')` → `1 day 03:00:00` | | `justify_interval(interval)` | `interval` | Adjust an interval using `justify_days` and `justify_hours`, with additional sign adjustments. | `justify_interval(interval '1 mon -1 hour')` → `29 days 23:00:00` | | `make_date(year int, month int, day int)` | `date` | Create a date from year, month, and day fields. | `make_date(2013, 7, 15)` → `2013-07-15` | | `make_time(hour int, min int, sec double precision)` | `time` | Create a time from hour, minute, and seconds fields. | `make_time(8,15, 23.5)` → `08:15:23.5` | | `make_timestamp(year int, month int, day int, hour int, min int, sec double precision)` | `timestamp` | Create a timestamp from year, month, day, hour, minute, and seconds fields. | `make_timestamp(2013, 7, 15, 8,15, 23.5)` → `2013-07-15 08:15:23.5` | | `minute(timestamp)` | `integer` | Return the minute from a timestamp (equivalent to `EXTRACT(MINUTE FROM ...)`). | `minute(timestamp '2001-02-16 20:38:40')` → `38` | | `minute(interval)` | `integer` | Return the minute from an interval (equivalent to `EXTRACT(MINUTE FROM ...)`). | `minute(interval '30 minutes')` → `30` | | `month(timestamp)` | `integer` | Return the month from a timestamp (equivalent to `EXTRACT(MONTH FROM ...)`). | `month(timestamp '2001-02-16 20:38:40')` → `2` | | `month(interval)` | `integer` | Return the month from an interval (equivalent to `EXTRACT(MONTH FROM ...)`). | `month (interval '6 months')` → `6` | | `now()` | `timestamp with time zone` | Return the current date and time with time zone (start of current statement). See [Current Date/Time](#current-datetime). | | | `quarter(timestamp)` | `integer` | Return the quarter from a timestamp (equivalent to `EXTRACT(QUARTER FROM ...)`). | `quarter(timestamp '2001-02-16 20:38:40')` → `1` | | `second(timestamp)` | `numeric` | Return the second from a timestamp (equivalent to `EXTRACT(SECOND FROM ...)`). | `second(timestamp '2001-02-16 20:38:40.500')` → `40.500` | | `second (interval)` | `numeric` | Return the second from an interval (equivalent to `EXTRACT(SECOND FROM ...)`) | `second(interval '21 seconds')` → `21.0` | | `timestamp at time zone 'timezone'` | `timestamptz` | Convert the timestamp in the specified time zone to timestamptz with the default time zone. | `timestamp '2001-08-16 20:38:40' at time zone 'Europe/Berlin'` → `2001-08-16 18:38:40+00` (assume the default time zone is UTC) | | `timestamptz at time zone 'timezone'` | `timestamp` | Convert the timestamptz to the timestamp in the specified time zone. | `timestamptz '2001-08-16 18:38:40+00' at time zone 'Europe/Berlin'` → `2001-08-16 20:38:40` | | `timestamp at time zone interval 'interval'` | `timestamptz` | Convert the timestamp in the UTC offset defined by `interval` to timestamptz with the default time zone. | `timestamp '2001-08-16 20:38:40' at time zone interval '+2:00'` → `2001-08-16 18:38:40+00` (assume the default time zone is UTC) | | `timestamptz at time zone interval 'interval'` | `timestamp` | Convert the timestamptz to the timestamp in the UTC offset defined by `interval`. | `timestamptz '2001-08-16 18:38:40+00' at time zone interval '+2:00'` → `2001-08-16 20:38:40` | | `to_timestamp(double precision)` | `timestamp with time zone` | Convert a Unix epoch (seconds since 1970-01-01 00:00:00+00) to a timestamp. | `to_timestamp(1284352323)` → `2010-09-13 04:32:03+00` | | `week(timestamp)` | `integer` | Return the week from a timestamp (equivalent to `EXTRACT(WEEK FROM ...)`). | `week(timestamp '2001-02-16 20:38:40')` → `7` | | `year(timestamp)` | `integer` | Return the year from a timestamp (equivalent to `EXTRACT(YEAR FROM ...)`). | `year(timestamp '2001-02-16 20:38:40')` → `2001` | | `year (interval)` | `integer` | Return the year from an interval (equivalent to `EXTRACT(YEAR FROM ...)`). | `year(interval '8 years')` → `8` | ## `EXTRACT` and `date_part` The `extract` and `date_part` are equivalent functions. These functions retrieve subfields such as year or hour from date and time values. The `extract` function is primarily used for computational processing. To display date or time values, see [Formatting Functions](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/formatting-func/formatting-func.md). ```sql EXTRACT( FROM [ , , ...] [ , , ...] ) ``` - `` - : An identifier or string that selects what field to extract from the source value. `EXTRACT` and `date_part` support the same field options. For valid options, see [Fields](#fields). - `` - : The type and value to extract the field from. Must be a value expression of type `date`, `timestamp`, `timestamptz`, `time`, or `interval`. (These functions cast expressions of type `date` to `timestamp`) - `` - : The fiscal calendar options define the start date of the fiscal calendar. `` must be of type `date`, `timestamp` or `timestamptz`, and `` must be `fiscal_week`, `fiscal_month`, `fiscal_quarter`, or `fiscal_year`. See [Fiscal Calendar Options](#fiscal-calendar-options). - `` - : The localized week options can define the start of a week and a year. `` must be type `date`, `timestamp,` or `timestamptz`, and `` is `localized_week`. See [Localized Week Options](#localized-week-options). The result type of `extract` depends on the given ``. The result type of `date_part` is always `double precision`, independent of the selected field. - `numeric(8,6)` for field `second` - `numeric(18,6)` for field `epoch` - `integer` for any other field ### Fields - `century` - : The century. The first century starts at 0001-01-01 00:00:00 AD. There's no century number 0\. ```sql SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13'); Result: 20 SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 21 ``` - `day` - : For `timestamp` values, the day of the month field (1 \- 31\). For `interval` values, the number of days. ```sql SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 16 SELECT date_part(DAY FROM INTERVAL '40 days 1 minute'); Result: 40 ``` - `decade` - : The year field divided by 10 ```sql SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 200 ``` - `dow` - : The day of the week as Sunday (`0`) to Saturday (`6`). ```sql SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 5 ``` - `doy` - : The day of the year (1 \- 365/366) ```sql SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 47 ``` - `epoch` - : For `timestamp with time zone` values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative) for `date` and `timestamp` values, the number of seconds since 1970-01-01 00:00:00 local time for `interval` values, the total number of seconds in the interval ```sql SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08'); Result: 982384720.12 SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); Result: 442800 ``` You can convert an epoch value back to a time stamp with `to_timestamp`. ```sql SELECT to_timestamp(982384720.12); Result: 2001-02-17 04:38:40.12+00 ``` - `hour` - : The hour field (0 \- 23\) ```sql SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 20 ``` - `fiscal_month` - : The month within the fiscal year (1 \- 12\) ```sql SELECT EXTRACT(FISCAL_MONTH FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 2 ``` - `fiscal_quarter` - : The quarter within the fiscal year (1 \- 4\) ```sql SELECT EXTRACT(FISCAL_QUARTER FROM TIMESTAMPTZ '2001-02-16 20:38:40'); Result: 1 ``` - `fiscal_week` - : The week within the fiscal year (1 \- 54\) ```sql SELECT EXTRACT(FISCAL_WEEK FROM TIMESTAMP '2001-12-16 20:38:40'); Result: 50 ``` - `fiscal_year` - : The fiscal year ```sql SELECT EXTRACT(FISCAL_YEAR FROM TIMESTAMP '2001-12-16 20:38:40'); Result: 2001 ``` - `isodow` - : The day of the week as Monday (`1`) to Sunday (`7`). This is identical to `dow` except for Sunday. ```sql SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40'); Result: 7 ``` - `isoyear` - : The ISO 8601 week-numbering year that the date falls in (not applicable to intervals). Each ISO 8601 week-numbering year begins with the Monday of the week containing 4 January. In early January or late December, the ISO year can be different from the Gregorian year. See the `week` field for more information. ```sql SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01'); Result: 2005 SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02'); Result: 2006 ``` - `localized_week` - : The week within the year with a custom start date, which is determined by the parameters `first_day_of_localized_week` and `minimal_days_in_localized_first_week`. See [Localized Week Options](#localized-week-options) for detailed explanation and examples. - `microseconds` - : The seconds field, including fractional parts, multiplied by 1000000. ```sql SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5'); Result: 28500000 ``` - `millennium` - : The millennium. Years in the 1900s are in the second millennium. The third millennium started January 1, 2001\. ```sql SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 3 ``` - `milliseconds` - : The seconds field, including fractional parts, multiplied by 1000\. ```sql SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5'); Result: 28500 ``` - `minute` - : The minutes field (0 \- 59\) ```sql SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 38 ``` - `month` - : For `timestamp` values, the number of the month within the year (1 \- 12\) For `interval` values, the number of months, modulo 12 (0 \- 11\) ```sql SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 2 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months'); Result: 3 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months'); Result: 1 ``` - `quarter` - : The quarter of the year (1 \- 4\) that the date is in ```sql SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 1 ``` - `second` - : The seconds field, including fractional parts (0 \- 59; can be 60 on leap seconds) ```sql SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 40 SELECT EXTRACT(SECOND FROM TIME '17:12:28.5'); Result: 28.5 ``` - `timezone` - : The time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC. - `timezone_hour` - : The hour part of the time zone offset - `timezone_minute` - : The minute part of the time zone offset - `week` - : The number of the ISO 8601 week-numbering week of the year. By definition, ISO weeks start on Mondays and the first week of a year contains January 4 of that year. For example, the first Thursday of a year is in week 1 of that year. In the ISO week-numbering system, early January dates can be in the 52nd or 53rd week of the year before. Late December dates can be in the first week of the next year. For example, `2005-01-01` is part of the 53rd week of year 2004, and `2006-01-01` is part of the 52nd week of year 2005, while `2012-12-31` is part of the first week of 2013\. It’s recommended that you use the `isoyear` field together with `week` to get consistent results. ```sql SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 7 ``` - `year` - : The year field. There's no `0 AD`, so subtract `BC` years from `AD` years with care. ```sql SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 2001 ``` ## `date_trunc` The `date_trunc` function truncates any timestamp expression or literal based on the time interval that you specify, such as hour, day, or month. ```sql date_trunc( , [, ] [, [, ...] ] [, first_day_of_localized_week => ] ) ``` - `` - : Define the precision for truncating the input value. Sets all other fields to zero (or one, for day and month). Valid values: `microseconds`, `milliseconds`, `second`, `minute`, `hour`, `day`, `week`, `localized_week`, `fiscal_week`, `month`, `fiscal_month`, `quarter`, `fiscal_quarter`, `year`, `fiscal_year`, `decade`, `century`, and `millennium`. Return value: timestamp, timestamp with time zone, or interval. - `` - : The value to truncate. Valid values: `timestamp`, `timestamp with time zone`, or `interval`. (`date_trunc` casts values of type `date` and `time` to `timestamp` or `interval`, respectively.) Limitations: `` can't be of type `interval` if `` is `week` because a month can contain a fractional number of weeks. You can't provide a time zone with `timestamp without time zone` or `interval` inputs. With type `timestamp with time zone`, `date_trunc` performs the truncation with respect to a defined time zone. For example, truncation to `day` produces a value that is midnight in that zone. By default, `date_trunc` uses the current time zone. - `` - : The fiscal calendar options define the start date of the fiscal calendar. `` must be of type `date`, `timestamp` or `timestamptz`, and `` must be `fiscal_week`, `fiscal_month`, `fiscal_quarter`, or `fiscal_year`. See [Fiscal Calendar Options](#fiscal-calendar-options). - `` - : The localized week options define the start of a week and a year. `` must be type `date`, `timestamp,` or `timestamptz`, and `` is `localized_week`. See [Localized Week Options](#localized-week-options). Examples, assuming the local time zone is `America/New_York`: ```sql SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40'); Result: 2001-02-16 20:00:00 SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40'); Result: 2001-01-01 00:00:00 SELECT date_trunc('hour', INTERVAL '3 days 02:47:33'); Result: 3 days 02:00:00 SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00'); Result: 2001-02-16 00:00:00-05 SELECT date_trunc('fiscal_year', TIMESTAMP '2001-02-16 20:38:40', fiscal_year_start_month => 2); Result: 2001-02-01 00:00:00 ``` ## Current Date/Time These functions return values based on the start time of the current statement. - `CURRENT_DATE` - `CURRENT_TIME CURRENT_TIMESTAMP CURRENT_TIME()` - `CURRENT_TIMESTAMP()` The return value of both `CURRENT_TIME` and `CURRENT_TIMESTAMP` contains the time zone. Some examples: ```sql SELECT CURRENT_TIME; Result: 14:39:53.662522-05 SELECT CURRENT_DATE; Result: 2001-12-23 SELECT CURRENT_TIMESTAMP; Result: 2001-12-23 14:39:53.662522-05 ``` Date and time data types accept the special literal value `now` to specify the current date and time. These three types all return the same result. ```sql SELECT CURRENT_TIMESTAMP; SELECT now(); SELECT TIMESTAMP 'now'; CREATE TABLE a (t TIMESTAMP DEFAULT 'now'); ``` The fourth example uses the current timestamp at the time of insertion of a tuple, and not the timestamp of table creation. ## Fiscal Calendar Options The fiscal calendar options configure standard or custom fiscal years in your query. The fiscal calendar can differ from the calendar based on your business needs. You can use the fiscal calendar options in [`EXTRACT`, `DATE_PART`](#extract-and-date_part), [`DATE_TRUNC`](#date_trunc), `DATEDIFF`, and [`TO_CHAR`](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/formatting-func/to-char.md) functions when the field is a fiscal date unit: ```sql fiscal_week fiscal_month fiscal_quarter fiscal_year ``` Options for Standard Fiscal Calendars: - `fiscal_year_start_month => ` : The first month of your fiscal year. Default: `1` (January). For example, if the fiscal year starts in February the `` is `2`. - `first_day_of_fiscal_week => ` : The first day of your fiscal week. Default: `1` (Monday). For example, if the fiscal year starts in January and `` is `1` (Monday), `fiscal_week` on date `2023-01-02` returns `2`, because `2023-01-01` is a Sunday making `2023-01-02` the second week of the fiscal year. - `use_start_date_as_fiscal_year_name => ` : Sets the naming for your fiscal year as the current year or the next year. Default: `true`. For example, the fiscal year starting at `2020-04-01` is 2020 if `value` is `true`. If `value` is `false`, the fiscal year is 2021. Options for Custom Fiscal Calendars: - `fiscal_period_table => TABLE()` : Uses a custom fiscal calendar that you define in a ``. The `` is a lookup table that maps each day to the fiscal periods. It has these columns: ```sql DayDate__c date -- The calendar date (lookup key for the table) YearNumber__c int -- The fiscal year number (e.g., 2025) QuarterNumber__c int -- Fiscal quarter within the year (1-4) MonthNumber__c int -- Fiscal month within the year (e.g., 1-12 or 1-13) WeekNumber__c int -- Fiscal week within the year FiscalYearFirstDayDate__c date -- First day of the fiscal year FiscalQuarterFirstDayDate__c date -- First day of the fiscal quarter FiscalMonthFirstDayDate__c date -- First day of the fiscal month FiscalWeekFirstDayDate__c date -- First day of the fiscal week RunningYearNumber__c int -- Absolute year index (1, 2, 3, ...) RunningQuarterNumber__c int -- Absolute quarter index (1, 2, 3, ...) RunningMonthNumber__c int -- Absolute month index (1, 2, 3, ...) RunningWeekNumber__c int -- Absolute week index (1, 2, 3, ...) ``` Notes: - You can't combine options for standard and custom fiscal calendars. - In Data 360, by default functions use the fiscal calendar configured in your Org. If you haven't configured a calendar, the function uses a standard fiscal calendar with the default values. Some examples: ```sql SELECT EXTRACT(fiscal_week FROM timestamp '2000-01-02 20:38:40', first_day_of_fiscal_week => 7); Result: 2 SELECT EXTRACT(fiscal_year FROM timestamp '2000-02-16 20:38:40', fiscal_year_start_month => 3); Result: 1999 SELECT EXTRACT(fiscal_year FROM timestamp '2000-02-16 20:38:40', use_start_date_as_fiscal_year_name => false); Result: 2001 SELECT datediff('fiscal_year', date '2025-02-01', date '2025-03-01', fiscal_year_start_month => 3); Result: 1 ``` ## Localized Week Options The localized week options define the start of a week and a year in the [EXTRACT, date_part](#extract-and-date_part), and [date_trunc](#date_trunc). Localized week options: - `first_day_of_localized_week => ` : The first day of the localized week. If `` is 1, every week and every year starts with a Monday. If `` is 7, the week and year start with a Sunday. - `minimal_days_in_localized_first_week => ` : This option defines the minimum number of days in the localized first week of the year for it to qualify as the first week of the year. The start of the first week of a year is the first weekday defined by `first_day_of_localized_week`. For the examples, use this calendar: | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | | :--------- | :--------- | :--------- | :--------- | :--------- | :--------- | :--------- | | 2021-12-27 | 2021-12-28 | 2021-12-29 | 2021-12-30 | 2021-12-31 | 2022-01-01 | 2022-01-02 | | 2022-01-03 | 2022-01-04 | 2022-01-05 | 2022-01-06 | 2022-01-07 | 2022-01-08 | 2022-01-09 | | ... | ... | ... | ... | ... | ... | ... | | 2022-12-26 | 2022-12-27 | 2022-12-28 | 2022-12-29 | 2022-12-30 | 2022-12-31 | 2023-01-01 | | 2023-01-02 | 2023-01-03 | 2023-01-04 | 2023-01-05 | 2023-01-06 | 2023-01-07 | 2023-01-08 | `EXTRACT` function for `localized_week`: ```sql SELECT EXTRACT(localized_week FROM timestamp '2023-01-01', first_day_of_localized_week => 1, minimal_days_in_localized_first_week => 1); Result: 1 ``` The first day of a week is set to Monday. The first day of the year 2023 is `2022-12-26`, because it is the first Monday of the week that has at least 1 day in year 2023. ```sql SELECT EXTRACT(localized_week FROM timestamp '2023-01-01', first_day_of_localized_week => 1, minimal_days_in_localized_first_week => 2); Result: 53 ``` The first day of a week is set to Monday. The first day of the year 2023 is `2023-01-02`, because it is the first Monday of the week that has at least 2 days in year 2023. ```sql SELECT EXTRACT(localized_week FROM timestamp '2023-01-01', first_day_of_localized_week => 1, minimal_days_in_localized_first_week => 3); Result: 52 ``` The first day of a week is set to Monday. The first day of the year 2023 is `2023-01-02`, because it is the first Monday of the week that has at least 3 days in year 2023.