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.
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
You can try with ADD_MONTHS function as well:
SEL BUSINESSDATE BETWEEN ADD_MONTHS(CAST('2014-06-06' AS DATE ),-24) AND DATE.
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 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
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)
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
SELECT ADD_MONTHS(( sel date)-EXTRACT(DAY FROM (sel date))+1,1)-1
This could be helpful for your reference.
SEL DATE - EXTRACT(DAY FROM DATE) + 1 FIRST_DAY, LAST_DAY(DATE) LAST_DATE ;
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
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?