need syntax for for last 2 years data

Database
Enthusiast

need syntax for for last 2 years data

Hi All,

please can anyvody provide sysntax for last 2 years data .

Where BusinessDate between to_char( current_date, -364 ) and to_char(current_date) is this is correct ?

Please let me know.

Thanks.

12 REPLIES
Enthusiast

Re: need syntax for for last 2 years data

Hi

I am assuming the businessdate field has data type 'Date'. If so then you can use below,

where businessdate between (date - interval '2' year) and date

Thanks

Santanu

Enthusiast

Re: need syntax for for last 2 years data

Hi,

You can try with ADD_MONTHS function as well:

SEL BUSINESSDATE BETWEEN ADD_MONTHS(CAST('2014-06-06' AS DATE ),-24) AND DATE.

Senior Apprentice

Re: need syntax for for last 2 years data

Don't use the INTERVAL syntax as it will fail on 2016-02-29, better use ADD_MONTHS instead.

Enthusiast

Re: need syntax for for last 2 years data

Thanks Dieter for your update.

Now I can see, SEL DATE '2016-02-29' - INTERVAL '4' YEAR ; will work but SEL DATE '2016-02-29' - INTERVAL '2' YEAR ; will not. So it should be ADD_MONTHS function.

Thanks

Santanu

Enthusiast

Re: need syntax for for last 2 years data

thanks for your inputs.

the req changed actually there are looking for

starting date of month and ending date of month

suppose  today date  is 06/10/2014 -> then we need 06/1/2014

 for end date is 06/10/2014 -> then we need 06/31/2014.

so it should be between "starting date of  month" and"  ending date of month"

please can any body help on syntax

Enthusiast

Re: need syntax for for last 2 years data

Hi , You can achive these date by this query-

Ist Day of Month :

SELECT ADD_MONTHS(cast('2014-06-10' as date)-EXTRACT(DAY FROM cast('2014-06-10' as date))+1,0)

or 

SELECT ADD_MONTHS((sel date)-EXTRACT(DAY FROM (sel date))+1,0)

Last Day of Month :

SELECT ADD_MONTHS(cast('2014-06-10' as date)-EXTRACT(DAY FROM cast('2014-06-10' as date))+1,1)-1

or

SELECT ADD_MONTHS(( sel date)-EXTRACT(DAY FROM (sel  date))+1,1)-1

                                                                                                                 Amit

Enthusiast

Re: need syntax for for last 2 years data

This could be helpful for your reference.

SEL DATE - EXTRACT(DAY FROM DATE) + 1 FIRST_DAY, LAST_DAY(DATE) LAST_DATE ;

Thanks

Santanu

Senior Apprentice

Re: need syntax for for last 2 years data

There were already multile threads on first/last day of month.

first day = dt - (extract(day from dt) -1) 

last day = first day of the following month - 1 (or your TD version supports LAST_DAY)

You can get any month by finding the first day and then ADD_MONTHS.

For the current month it's 

           CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE)-1),
ADD_MONTHS(CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE)-1),1)-1
Enthusiast

Re: need syntax for for last 2 years data

Hi Dieter

Want to know your opinion. While calculating last day of month, does the use of add_months better option than last_day function? Is there any restriction on last_day function?

Thanks

Santanu