Print
All Forums Database
skchintha 13 posts Joined 12/13
04 Jun 2014
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.

Santanu84 122 posts Joined 04/13
04 Jun 2014

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

ravimans 54 posts Joined 02/14
06 Jun 2014

Hi,
You can try with ADD_MONTHS function as well:
SEL BUSINESSDATE BETWEEN ADD_MONTHS(CAST('2014-06-06' AS DATE ),-24) AND DATE.
 

dnoeth 4628 posts Joined 11/04
07 Jun 2014

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

Dieter

Santanu84 122 posts Joined 04/13
08 Jun 2014

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

skchintha 13 posts Joined 12/13
10 Jun 2014

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

Amit.Singh 5 posts Joined 06/14
11 Jun 2014

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

Amit

Santanu84 122 posts Joined 04/13
11 Jun 2014

This could be helpful for your reference.
SEL DATE - EXTRACT(DAY FROM DATE) + 1 FIRST_DAY, LAST_DAY(DATE) LAST_DATE ;
Thanks
Santanu

dnoeth 4628 posts Joined 11/04
11 Jun 2014

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

 

Dieter

Santanu84 122 posts Joined 04/13
11 Jun 2014

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

dnoeth 4628 posts Joined 11/04
11 Jun 2014

Hi Santanu,
of course LAST_DAY should be preferred if it exists on your system.
LAST_DAY was added in TD14, before it might be implemented as a C-UDF or SQL UDF 

Dieter

Santanu84 122 posts Joined 04/13
11 Jun 2014

Thanks Dieter for your answer and clarification.
Santanu

skchintha 13 posts Joined 12/13
11 Jun 2014

thanks guys its working
 

You must sign in to leave a comment.