Processing date will have 8/30/2012
Query: (Its failed for invalid date)
select processdate + (INTERVAL '1' DAY) - (INTERVAL '26' MONTH) FROM CONTROL_DATE;
6/31/2012 (We don't have 31st for June month)
My question is:
Using above logic how can we get the expected output. Can we use the days instead of month in the query. If so, please provide the sql.
Help me on this.
Thanks in advance.
Why do you expect 7/1/2010 if you go back 26 month from 8/31/2012?
I would expect 6/30/2012
which you get via
select cast('2012-08-30' as date) as base_dt,
07/01/2010 which is posted above is wrong it's my mistake. And one thing is, the sql you mentioned above the same logic i tried but its looking for 06/31/2012. will the above mentioned SQL will works?
X0.EVENT_START_DATE > ((X2.processing_date as date) as bs_date, add_months (bs_date+1,-26))
assume X0.EVENT_START_DATE is a date
> would require that the right hand side is also a date
But what is
((X2.processing_date as date) as bs_date, add_months (bs_date+1,-26))?
The outer () is covering two expressions
(X2.processing_date as date) -> missing cast???
as bs_date -> wrong in comparisons
so what should
((X2.processing_date as date) as bs_date, add_months (bs_date+1,-26)) be?