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 (https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/language_for_defining_and_processing_data_vql/data_types/data_types_for_dates_timestamps_and_intervals) 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.
Date Conversion
user
11-05-2021 16:17:27 -0400
code

1 Answer

Hi, I am able to perform the subtraction from date by [defining an Interval](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/language_for_defining_and_processing_data_vql/data_types/data_types_for_dates_timestamps_and_intervals#data-types-for-intervals) 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](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/appendix/syntax_of_condition_functions/date_processing_functions#formatdate), and supplying the desired [Pattern String](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/appendix/date_and_time_pattern_strings/date_and_time_pattern_strings#date-and-time-pattern-strings), 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
code
You must sign in to add an answer. If you do not have an account, you can register here