Skip to main content

day_of_year

1. DAY_OF_YEAR Function​

The DAY_OF_YEAR function in H2 database is used to retrieve the day of the year for a given date.

2. Syntax​

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

DAY_OF_YEAR(date)

Arguments​

  • date: The date for which the day of the year needs to be retrieved. It should be a valid date value or expression.

Return​

  • The DAY_OF_YEAR function returns an integer value representing the day of the year for the given date.

3. Notes​

  • The DAY_OF_YEAR function in H2 database considers the year to be 365 days long, and the day of the year ranges from 1 to 365 (or 366 in a leap year).
  • The input date should be a valid date value or expression in the supported format. If the input date is not valid or cannot be parsed, the function will return NULL.
  • Make sure to use the correct syntax and provide a valid date when using the DAY_OF_YEAR function to avoid errors.

4. Examples​

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

Example 1 - Retrieving the day of the year for a specific date:​

SELECT DAY_OF_YEAR(DATE '2022-10-15') AS day_of_year;

Output:

day_of_year
-----------
288

Example 2 - Retrieving the day of the year for a column of dates:​

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

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

SELECT id, event_date, DAY_OF_YEAR(event_date) AS day_of_year
FROM events;

Output:

id | event_date  | day_of_year
---+-------------+-------------
1  | 2022-01-01  | 1
2  | 2022-05-15  | 135
3  | 2022-12-31  | 365
  • year - Extract the year from a date
  • month - Extract the month from a date
  • DAY - Extract the day from a date