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() 2 Answers
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".
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 
