I am trying to run this as part of my Select statement to add 30 calander days to every date value in my dataset, though it is giving me an error '2665 Invalid date'
cast(Accts.Applied_Date_of_Switch as date format 'mm/dd/yyyy') + interval '30' DAY
can anyone help??
I ran below queries
1.sel cast('09/23/2016' as date format 'mm/dd/yyyy') + interval '30' DAY
2. sel cast('2016/09/23' as date ) + interval '30' DAY
3. sel cast('2016/09/23' as date format 'mm/dd/yyyy') + interval '30' DAY
First two worked but third one failed. Just check whether Accts.Applied_Date_of_Switc is in 'mm/dd/yyyy' format.
do cast(Accts.Applied_Date_of_Switch as date) and see whether you are able to cast it and then do addition
Maybe it's because of the data in your column.
If at least one value is bigger than 9999-12-01 then the calculation will go beyond 9999-12-31. And that's an invalid date.