This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
DECADE
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Extract the DECADE value from a datetime value.
The DECADE function is a short version of the EXTRACT, passing a DatePart.DECADE value as an argument.
SELECT decade(DATE '2020-02-03');
create.select(decade(Date.valueOf("2020-02-03"))).fetch();
The result being
+--------+ | decade | +--------+ | 202 | +--------+
Dialect support
This example using jOOQ:
decade(Date.valueOf("2020-02-03"))
Translates to the following dialect specific expressions:
-- ACCESS
(cdec((datepart('yyyy', #2020/02/03 00:00:00#) / 10)) - ((datepart('yyyy', #2020/02/03 00:00:00#) / 10) < cdec((datepart('yyyy', #2020/02/03 00:00:00#) / 10))))
-- ASE, SYBASE
floor((datepart(yy, '2020-02-03 00:00:00.0') / 10))
-- AURORA_MYSQL, MEMSQL, MYSQL
floor((extract(YEAR FROM {ts '2020-02-03 00:00:00.0'}) / 10))
-- AURORA_POSTGRES, POSTGRES, YUGABYTEDB
extract(DECADE FROM TIMESTAMP '2020-02-03 00:00:00.0')
-- BIGQUERY
floor((extract(YEAR FROM DATETIME '2020-02-03 00:00:00.0') / 10))
-- COCKROACHDB, DUCKDB, EXASOL, FIREBIRD, H2, HANA, HSQLDB, MARIADB, ORACLE, REDSHIFT, SNOWFLAKE, TERADATA, TRINO,
-- VERTICA
floor((extract(YEAR FROM TIMESTAMP '2020-02-03 00:00:00.0') / 10))
-- DB2
floor((YEAR(TIMESTAMP '2020-02-03 00:00:00.0') / 10))
-- DERBY
floor((YEAR(TIMESTAMP('2020-02-03 00:00:00.0')) / 10))
-- INFORMIX
floor((YEAR(DATETIME(2020-02-03 00:00:00.0) YEAR TO FRACTION) / 10))
-- SQLDATAWAREHOUSE, SQLSERVER
floor((datepart(yy, CAST('2020-02-03 00:00:00.0' AS DATETIME2)) / 10))
-- SQLITE
floor((CAST(strftime('%Y', '2020-02-03 00:00:00.0') AS int) / 10))
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!