Have a requirement in which I have to search particular day for the current month , the query is running .. For ex . If I am running query for May'2013 the where condition should be resolved to ( where date = 05/11/2013 ) ( Where 11 is the static value )
I tried this using below option , wanted to know if there is any better way of doing this , as I have to use couple of values using IN clause , For ex. ( where date in 05/11/2013 , 05/15/2013 , 06/11/2013 )
Here is the where condition I am using
cycle_date in (
when EXTRACT(MONTH FROM DATE) < 9
then cast ( '0'|| trim(trim(EXTRACT(MONTH FROM DATE)) || '/11/' || trim(EXTRACT(year FROM DATE))) as date format 'mm/dd/yyyy')
cast ( trim(trim(EXTRACT(MONTH FROM DATE)) || '/11/' || trim(EXTRACT(year FROM DATE))) as date format 'mm/dd/yyyy')
To get the nth of a month (n=0 returns the last day of the previous month):
DATE - (EXTRACT(DAY FROM DATE ) - n)
Caution, this works for a single calculation in an IN clause, but when you add a second value you'll get a strange syntax error message.
You must rewrite it as multiple ORed conditions instead.
Don't ask me why the parser insists upon that, as it's automatically rewriting any IN into the same ORed conditions :-)
Thanks Dieter ..
I was about to ask the same question as I got error while trying more than one value in the IN clause using my old code aswell . :-)