I have a date column ( d1) in YYYY-MM format and is stored as a string/character.
I need to apply a filter which gives me past 12 months data, ex: d1 between _add_months(sysdate,-1) and _add_months(sysdate,-12)
But I'm getting errors while writing it, mainly due to string date.
Can anyone pls suggest on how to write it.
We are using Oracle database.
Many thanks in advance
Actually you don't have a date. You have a month and year. You need a day along with month and year to cast to a true date data type.
What is your conversion expression and what errors are you getting? You could try something like this to add the 1st day to your month and year and cast as date:
cast ( [YourString] || '-01' , DATE )
Once you can reliably get a date, then use _add_months to do the date math.
Note that the order of your "between" components is backwards. The earlier date will be -12 and the later date will be -1. Should be:
between _add_months( [YourConvertedDate], -12 ) and _add_months ( [YourConvertedDate, -1 )
Lastly, there may be a performance implication of filtering your data using a derived column if the optimizer doesn't figure out what you are actually trying to do. Figuring out the two dates (1 month ago and 12 months ago), and then stripping out year and month, and then formatting back to a string might be the way to go if there is indexing on that field (assuming the optimizer doesn't pick up on it based on the conversion).
Hi Lynn,
Thanks for your reply.
Using the cast function ( by adding day) that field has converted to date datatype.
Can you tell me how shall I write the rolling 12 months logic for my new date dataype field 'd2'...when I use 'between', i'm getting only 2 months of data ( first month and 12th month)
My issue got fixed now....thanks Lynn for your clues.
I wrote wrong expression using 'between' ( didn't gave attention to your warning !!!), now corrected it.
Many thanks.