New versions: Dev (3.14) | Latest (3.13) | 3.12

DAY_OF_YEAR

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Extract the DAY_OF_YEAR value from a datetime value.

The DAY_OF_YEAR function is a short version of the EXTRACT, passing a DatePart.DAY_OF_YEAR value as an argument.

SELECT day_of_year(DATE '2020-02-03');
create.select(dayOfYear(Date.valueOf("2020-02-03"))).fetch();

The result being

+-------------+
| day_of_year |
+-------------+
|          33 |
+-------------+

Dialect support

This example using jOOQ:

dayOfYear(Date.valueOf("2020-02-03"))

Translates to the following dialect specific expressions:

-- ASE, SYBASE
datepart(dy, '2020-02-03 00:00:00.0')

-- AURORA_MYSQL, MEMSQL, MYSQL
dayofyear({ts '2020-02-03 00:00:00.0'})

-- AURORA_POSTGRES, COCKROACHDB, POSTGRES
extract(DOY FROM TIMESTAMP '2020-02-03 00:00:00.0')

-- DB2, MARIADB
dayofyear(TIMESTAMP '2020-02-03 00:00:00.0')

-- H2, HSQLDB
extract(DAY_OF_YEAR FROM TIMESTAMP '2020-02-03 00:00:00.0')

-- ORACLE
to_number(to_char(TIMESTAMP '2020-02-03 00:00:00.0', 'DDD'))

-- SQLDATAWAREHOUSE, SQLSERVER
datepart(dy, CAST('2020-02-03 00:00:00.0' AS DATETIME2))

-- SQLITE
strftime('%j', '2020-02-03 00:00:00.0')

-- ACCESS, CUBRID, DERBY, FIREBIRD, HANA, INFORMIX, INGRES, REDSHIFT, TERADATA, VERTICA
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.14, see #10141)

The jOOQ Logo