Skip to main content

week

1. WEEK Function​

The WEEK function in H2 database is used to retrieve the week number for a given date or timestamp.

2. Syntax​

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

WEEK(date)

Arguments​

  • date: The date or timestamp value for which the week number needs to be determined. It can be a DATE, TIMESTAMP, or VARCHAR value representing a valid date or timestamp.

Return​

  • The WEEK function returns the week number for the given date or timestamp. The week number is an integer value between 1 and 53.

3. Notes​

  • The WEEK function in H2 database follows the ISO 8601 week numbering system, where the first week of the year is the week that contains at least 4 days of the new year.
  • If the input date or timestamp is not a valid value or in an unsupported format, the WEEK function will return NULL.
  • Be aware that different databases may have different implementations of the WEEK function, so the behavior may vary when compared to other database systems.

4. Examples​

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

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

SELECT WEEK(DATE '2022-03-12') AS week_number;

Output:

week_number
-----------
10

Example 2 - Retrieving the week number for a timestamp column:​

CREATE TABLE events (
  id INT PRIMARY KEY,
  event_name VARCHAR(100),
  event_date TIMESTAMP
);

INSERT INTO events VALUES (1, 'Event A', TIMESTAMP '2022-02-28 15:30:00'),
                          (2, 'Event B', TIMESTAMP '2022-03-05 09:45:00'),
                          (3, 'Event C', TIMESTAMP '2022-03-20 18:00:00');

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

Output:

id | event_name |        event_date         | week_number
---+------------+---------------------------+-------------
1  | Event A    | 2022-02-28 15:30:00.000000 | 9
2  | Event B    | 2022-03-05 09:45:00.000000 | 10
3  | Event C    | 2022-03-20 18:00:00.000000 | 12
  • DAY - Retrieve the day of the month from a date or timestamp
  • month - Retrieve the month from a date or timestamp
  • year - Retrieve the year from a date or timestamp