The jOOQ User Manual : SQL building : Column expressions : Datetime functions : DATEDIFF | previous : next |
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 | +------------+
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+
Feedback
Do you have any feedback about this page? We'd love to hear it!