Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
DATEDIFF
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Subtract two SQL DATE types (represented by java.sql.Date).
This function comes in two flavours:
MySQL 2 argument version
In MySQL, there is a 2 argument verison of the DATEDIFF() function, where the result produces the number of days between the two dates. The argument order is in the order of the difference notation: end_date - start_date
SELECT DATEDIFF( DATE '2020-02-03', DATE '2020-02-01');
create.select(dateDiff(
Date.valueOf("2020-02-03"),
Date.valueOf("2020-02-01"))).fetch();
The result being
+------------+ | datediff | +------------+ | 2 | +------------+
Dialect support
This example using jOOQ:
dateDiff(Date.valueOf("2020-02-03"), Date.valueOf("2020-02-01"))
Translates to the following dialect specific expressions:
Access
datediff('d', #2020/02/01#, #2020/02/03#)
ASE, Sybase
datediff(DAY, '2020-02-01', '2020-02-03')
Aurora MySQL, MariaDB, MySQL
datediff(DATE '2020-02-03', DATE '2020-02-01')
Aurora Postgres, CockroachDB, Oracle, Postgres, YugabyteDB
(DATE '2020-02-03' - DATE '2020-02-01')
BigQuery
date_diff(DATE '2020-02-03', DATE '2020-02-01', DAY)
ClickHouse, Teradata
CAST((DATE '2020-02-03' - DATE '2020-02-01') AS integer)
Databricks, Exasol
CAST((DATE '2020-02-03' - DATE '2020-02-01') AS int)
DB2
(days(DATE '2020-02-03') - days(DATE '2020-02-01'))
Derby
{fn timestampdiff(sql_tsi_day, DATE('2020-02-01'), DATE('2020-02-03')) }
DuckDB, Redshift
datediff('day', DATE '2020-02-01', DATE '2020-02-03')
Firebird, H2, HSQLDB, Snowflake, Vertica
datediff(DAY, DATE '2020-02-01', DATE '2020-02-03')
Hana
days_between(DATE '2020-02-01', DATE '2020-02-03')
Informix
CAST((DATETIME(2020-02-03) YEAR TO DAY - DATETIME(2020-02-01) YEAR TO DAY) AS integer)
MemSQL
datediff({d '2020-02-03'}, {d '2020-02-01'})
SQLDataWarehouse, SQLServer
datediff(DAY, CAST('2020-02-01' AS date), CAST('2020-02-03' AS date))
SQLite
(strftime('%s', '2020-02-03') - strftime('%s', '2020-02-01')) / 86400
Trino
date_diff('day', DATE '2020-02-01', DATE '2020-02-03')
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
SQL Server 3 argument version
In SQL Server, there is a 3 argument verison of the DATEDIFF() function, where the result produces the number of date part periods between the two dates, with the dates being TRUNC-ed to the relevant date part. The argument order is in the order of the interval notation: [start_date, end_date]
SELECT DATEDIFF( MONTH DATE '2020-02-03', DATE '2020-04-01');
create.select(dateDiff(
DatePart.MONTH,
Date.valueOf("2020-02-03"),
Date.valueOf("2020-04-01"))).fetch();
The result being
+------------+ | datediff | +------------+ | 2 | +------------+
Notice the truncation happening prior to calculating the difference. The result is the same as for:
SELECT DATEDIFF( MONTH DATE '2020-02-01', DATE '2020-04-01');
create.select(dateDiff(
DatePart.MONTH,
Date.valueOf("2020-02-01"),
Date.valueOf("2020-04-01"))).fetch();
Dialect support
This example using jOOQ:
dateDiff(DatePart.MONTH, Date.valueOf("2020-02-03"), Date.valueOf("2020-04-01"))
Translates to the following dialect specific expressions:
Aurora MySQL, Aurora Postgres, CockroachDB, Hana, MariaDB, MySQL, Oracle, Postgres, YugabyteDB
(((extract(YEAR FROM DATE '2020-04-01') - extract(YEAR FROM DATE '2020-02-03')) * 12) + (extract(MONTH FROM DATE '2020-04-01') - extract(MONTH FROM DATE '2020-02-03')))
BigQuery
date_diff(DATE '2020-04-01', DATE '2020-02-03', MONTH)
DB2
(((YEAR(DATE '2020-04-01') - YEAR(DATE '2020-02-03')) * 12) + (MONTH(DATE '2020-04-01') - MONTH(DATE '2020-02-03')))
Derby
(((YEAR(DATE('2020-04-01')) - YEAR(DATE('2020-02-03'))) * 12) + (MONTH(DATE('2020-04-01')) - MONTH(DATE('2020-02-03'))))
Firebird, H2, HSQLDB, Snowflake
datediff(MONTH, DATE '2020-02-03', DATE '2020-04-01')
MemSQL
(((extract(YEAR FROM {d '2020-04-01'}) - extract(YEAR FROM {d '2020-02-03'})) * 12) + (extract(MONTH FROM {d '2020-04-01'}) - extract(MONTH FROM {d '2020-02-03'})))
Redshift
datediff('month', DATE '2020-02-03', DATE '2020-04-01')
SQLDataWarehouse, SQLServer
datediff(MONTH, CAST('2020-02-03' AS date), CAST('2020-04-01' AS date))
ASE, Access, ClickHouse, Databricks, DuckDB, Exasol, Informix, SQLite, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!