New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | 2.6

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, CUBRID, ORACLE, POSTGRES
(DATE '2020-02-03' - DATE '2020-02-01')

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

-- FIREBIRD, H2, HSQLDB, 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)

-- INGRES, TERADATA
CAST((DATE '2020-02-03' - DATE '2020-02-01') AS integer)

-- MARIADB
datediff(DATE '2020-02-03', DATE '2020-02-01')

-- REDSHIFT
datediff('day', DATE '2020-02-01', DATE '2020-02-03')

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

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

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]. This version is supported only in jOOQ 3.14+

The jOOQ Logo