Skip to main content


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:



  • 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.


  • 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;



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

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;


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