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

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, MemSQL, MySQL

datediff({d '2020-02-03'}, {d '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)

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')

Exasol

CAST((DATE '2020-02-03' - DATE '2020-02-01') AS int)

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)

MariaDB

datediff(DATE '2020-02-03', DATE '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')

(These are currently generated with jOOQ 3.20, see #10141), or translate your own 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, MemSQL, MySQL

(((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'})))

Aurora Postgres, CockroachDB, Hana, MariaDB, 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')

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, DuckDB, Exasol, Informix, SQLite, Sybase, Teradata, Trino, Vertica

/* UNSUPPORTED */

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

Feedback

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

The jOOQ Logo