Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10

DATESUB

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

Subtract an interval of type java.lang.Number (number of days) or org.jooq.types.Interval (SQL interval type) from a date (represented by java.sql.Date).

SELECT DATE '2020-02-03' - 2;
create.select(dateSub(Date.valueOf("2020-02-03"), 2)).fetch();

The result being

+------------+
| date_sub   |
+------------+
| 2020-02-01 |
+------------+

Dialect support

This example using jOOQ:

dateSub(Date.valueOf("2020-02-03"), 2)

Translates to the following dialect specific expressions:

Access

dateadd('d', -2, #2020/02/03#)

ASE, Sybase

dateadd(DAY, -2, '2020-02-03')

Aurora MySQL, MariaDB, MySQL

date_add(DATE '2020-02-03', INTERVAL -2 DAY)

Aurora Postgres, CockroachDB, Postgres, Redshift, YugabyteDB

(DATE '2020-02-03' + -2)

BigQuery

timestamp_sub(DATE '2020-02-03', INTERVAL 2 DAY)

ClickHouse, Exasol, H2, Oracle, Vertica

(DATE '2020-02-03' - 2)

DB2, HSQLDB

(DATE '2020-02-03' - (2) day)

Derby

CAST({fn timestampadd(SQL_TSI_DAY, -2, DATE('2020-02-03')) } AS DATE)

DuckDB

CAST(date_add(DATE '2020-02-03', INTERVAL 1 DAY * -2) AS DATE)

Firebird, Snowflake

dateadd(DAY, -2, DATE '2020-02-03')

Hana

add_days(DATE '2020-02-03', -2)

Informix

(DATETIME(2020-02-03) YEAR TO DAY - 2 UNITS DAY)

MemSQL

date_add({d '2020-02-03'}, INTERVAL -2 DAY)

SQLDataWarehouse, SQLServer

dateadd(DAY, -2, CAST('2020-02-03' AS date))

SQLite

strftime('%Y-%m-%d %H:%M:%f', '2020-02-03', (CAST(-2 AS varchar) || ' day'))

Teradata

DATE '2020-02-03' - CAST(2 || ' 00:00:00' AS INTERVAL DAY TO SECOND)

Trino

date_add('day', -2, DATE '2020-02-03')
Generated with jOOQ 3.20. Translate your own SQL on our website

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo