Skip to main content

day_of_week

1. DAY_OF_WEEK Function​

The DAY_OF_WEEK function in H2 database is used to retrieve the day of the week from a given date.

2. Syntax​

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

DAY_OF_WEEK(date)

Arguments​

  • date: The date from which the day of the week needs to be extracted. It can be a date literal, a column name of a date type, or an expression that evaluates to a date.

Return​

  • The DAY_OF_WEEK function returns an integer representing the day of the week. The returned value ranges from 1 to 7, where 1 represents Sunday, 2 represents Monday, and so on.

3. Notes​

  • The DAY_OF_WEEK function in H2 database follows the ISO-8601 standard, where Monday is considered the first day of the week and Sunday is the last day.
  • If the input date is not a valid date or is in an invalid format, the DAY_OF_WEEK function will return NULL.
  • Make sure to use the correct date format and consider any regional settings while using the DAY_OF_WEEK function to avoid unexpected results.

4. Examples​

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

Example 1 - Retrieving the day of the week from a specific date:​

SELECT DAY_OF_WEEK(DATE '2022-10-31') AS day;

Output:

day
---
2

In this example, the date '2022-10-31' is a Monday, so the DAY_OF_WEEK function returns 2.

Example 2 - Retrieving the day of the week from a column of dates:​

CREATE TABLE events (
  event_id INT PRIMARY KEY,
  event_date DATE
);

INSERT INTO events VALUES (1, DATE '2022-08-15'), (2, DATE '2022-09-03'), (3, DATE '2022-11-25');

SELECT event_id, event_date, DAY_OF_WEEK(event_date) AS day
FROM events;

Output:

event_id | event_date  | day
---------+-------------+----
1        | 2022-08-15  | 2
2        | 2022-09-03  | 7
3        | 2022-11-25  | 6

In this example, the DAY_OF_WEEK function is applied to the event_date column, retrieving the respective day of the week for each event date.

  • year - Retrieve the year from a date
  • month - Retrieve the month from a date
  • DAY - Retrieve the day from a date