Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
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)
-- 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, 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')
-- 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
-- TERADATA
CAST((DATE '2020-02-03' - DATE '2020-02-01') AS integer)
-- TRINO
date_diff('day', DATE '2020-02-01', DATE '2020-02-03')
(These are currently generated with jOOQ 3.19, 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))
-- ACCESS, ASE, DUCKDB, EXASOL, INFORMIX, SQLITE, SYBASE, TERADATA, TRINO, VERTICA
/* UNSUPPORTED */
(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!