Skip to main content

iso_week

1. ISO_WEEK Function​

The ISO_WEEK function in H2 database is used to determine the ISO week number for a given date.

2. Syntax​

The syntax for the ISO_WEEK function in H2 database is as follows:

ISO_WEEK(date)

Arguments​

  • date: The date for which the ISO week number needs to be determined. It should be a valid date value.

Return​

  • The ISO_WEEK function returns the ISO week number for the given date as an integer.

3. Notes​

  • The ISO_WEEK function in H2 database follows the ISO 8601 standard for week numbering. According to this standard, a week starts on Monday and the first week of the year is the week that contains the first Thursday.
  • The ISO_WEEK function considers the week containing January 4th as the first week of the year. Therefore, dates before January 4th may have a week number of the previous year.
  • If the input date is not a valid date or is in an invalid format, the ISO_WEEK function will return NULL.
  • Ensure that the date format used matches the format expected by H2 database to avoid errors.

4. Examples​

Here are a few examples demonstrating the usage of the ISO_WEEK function in H2 database:

Example 1 - Determining the ISO week number for a specific date:​

SELECT ISO_WEEK(DATE '2022-01-01') AS week_number;

Output:

week_number
-----------
52

Example 2 - Determining the ISO week number for a column of dates:​

CREATE TABLE events (
  id INT PRIMARY KEY,
  event_name VARCHAR(50),
  event_date DATE
);

INSERT INTO events VALUES (1, 'Event 1', DATE '2022-01-01'),
                          (2, 'Event 2', DATE '2022-02-15'),
                          (3, 'Event 3', DATE '2022-12-31');

SELECT id, event_name, event_date, ISO_WEEK(event_date) AS week_number
FROM events;

Output:

id | event_name | event_date  | week_number
---+------------+-------------+------------
1  | Event 1    | 2022-01-01  | 52
2  | Event 2    | 2022-02-15  | 7
3  | Event 3    | 2022-12-31  | 52
  • year - Extract year from a date
  • month - Extract month from a date
  • DAY - Extract day from a date