Using static Day value in a particular Date

Database
Enthusiast

Using static Day value in a particular Date

Hi ..

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

where

cycle_date   in  (

case

    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')

    else

        cast (  trim(trim(EXTRACT(MONTH FROM DATE)) || '/11/' || trim(EXTRACT(year  FROM DATE))) as date format 'mm/dd/yyyy')

end

Tags (1)
2 REPLIES
Senior Apprentice

Re: Using static Day value in a particular Date

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 :-)

Dieter

Enthusiast

Re: Using static Day value in a particular Date

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 .   :-)