You can translate the question and the replies:

Date subtraction

I am trying to subtract 4 months from the current date (without time) and then convert it to numeric value in the YYYYMMDD format. Looking at the manual ( It looks like I start out with "Current_date - interval 4 month) however the parser does not seem to like that. According to the ctrl+space prompter I would use "subtract(current_date, intervalyearmonth 0004)" and the parser does not like that either. If I was using DB2 I would do it like this. (year(current date - 4 months)* 10000) + Month(current date - 4 months)* 100) + day(current date - 4 months)) But that obviously doesn't work here. Any help would be appreciated.
11-05-2021 16:17:27 -0400

1 Answer

Hi, I am able to perform the subtraction from date by [defining an Interval]( for the desired difference like so : `INTERVAL ‘4’ MONTH` And using the regular arithmetic operator with the date, like : `current_date - (INTERVAL ‘4’ MONTH)` This will output a date with a time, by default Denodo will name this ‘subtract’ (like the function call). I can format this value using the [FORMATDATE Function](, and supplying the desired [Pattern String](, in this case ‘yyyyMMdd’. Combining these two commands, I can write : `SELECT FORMATDATE('yyyyMMdd', subtract) from (SELECT(current_date - (INTERVAL '4' MONTH)))` Hope this helps!
Denodo Team
12-05-2021 17:10:35 -0400
You must sign in to add an answer. If you do not have an account, you can register here