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

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 User Manual : SQL building : Column expressions : Datetime functions : DATEDIFF

previous : next