Datediff in Db2 Query

Question

I have a query from mysql that has been running on a table I recently migrated to DB2.

The query now fails on DB2 due to the line below, saying that DATEDIFF cannot be found. I'm assuming only because this isn't a valid function on db2.

Is there an equivalent to this on DB2 that will maintain performance as well as function?

SELECT DISTINCT
    LEAST(180, DATEDIFF(curdate(), start_date)) as days
FROM table2
where expire_date > curdate()

Accepted Answer

I use the DAYS() function to convert the dates to numeric sequential numbers and then just subtract them, as in:

SELECT DISTINCT
    LEAST(180, DAYS(curdate()) - DAYS(start_date)) as days
FROM table2
where expire_date > curdate()

According to DB2's manual, DAYS() returns: "The result is 1 more than the number of days from January 1, 0001".

Answer 2

On Db2 11.1 (for Linux, Unix and Windows) and above, this will work

SELECT DISTINCT
    LEAST(180, DAYS_BETWEEN(current_date, start_date)) as days
FROM table2
where expire_date > current_date

2 Answers