Try this
Ashok K Rathi, April 13, 2004 - 10:25 am UTC
select add_months(add_months(to_date('01-feb-03') ,(rownum * 3)), -3) ,
(add_months(to_date('01-feb-03') ,(rownum * 3)) -1) QRT_END from all_objects where rownum < 100
April 13, 2004 - 10:54 am UTC
why -- that doesn't answer the question
question wanted four rows for a user input date.
A reader, April 13, 2004 - 3:33 pm UTC
Why -> Query looks more compact and may come to use when you need more then 4 rows .
One can get 4 rows just by changing the query rownum < 5
why it is not counting 30/31 days and if leap year 29 elase 28 ?
A reader, August 20, 2004 - 1:02 pm UTC
I just want to add 1 month to existing date.
but,..
I ran this query and it looks like it is not counting 30 or 31 days in the month ?
SELECT SYSDATE,
ADD_MONTHS (SYSDATE, 1),
ADD_MONTHS (SYSDATE, 2),
ADD_MONTHS (SYSDATE, 3),
ADD_MONTHS (SYSDATE, 4),
ADD_MONTHS (SYSDATE, 5),
ADD_MONTHS (SYSDATE, 6)
FROM DUAL;
output:
SYSDATE ADD_MONTHS(SYSDATE,1) ADD_MONTHS(SYSDATE,2) ADD_MONTHS(SYSDATE,3) ADD_MONTHS(SYSDATE,4) ADD_MONTHS(SYSDATE,5) ADD_MONTHS(SYSDATE,6)
8/20/2004 12:45:33 PM 9/20/2004 12:45:33 PM 10/20/2004 12:45:33 PM 11/20/2004 12:45:33 PM 12/20/2004 12:45:33 PM 1/20/2005 12:45:33 PM 2/20/2005 12:45:33 PM
all added dates are 20 . It don't count the moths30/31 days ?
August 21, 2004 - 11:02 am UTC
the documented output of add months is:
if you add N months to a date, and the date is not the last day of the month, you get the same day in the month that is N months from them
that is, if you add 1 month to august 20th, you will get the 20th of september -- that is the same day in the next month.
if you add 1 month to the last day of a month, you get the last day of the NEXT month.
that is what is it documented to do. that is what most people would expect.
Thanks,
A reader, August 23, 2004 - 5:48 pm UTC
so how do I get what I want ? can you guide me ?
August 23, 2004 - 7:44 pm UTC
describe "what you want" and we'll see.
put it into english, spec it out - no idea what you "want"
if you add a month to a date in august (with 31 days) should it be 30 or 31 days into the future. and if you added N months (n > 1) what then?????
How to get No. of Quarters between two Quarters?
A reader, June 14, 2006 - 11:22 am UTC
Lets say i have two quarters
Variable 1 : year=2005 and Quarter=4
Variable 2 : year=2004 and Quarter=2
I want to find how many quarters are there between these 2 quarters?
One solution i came up with [looks like a weird one..]
Convert yearqtr to a valid date ..
eg: 20054 -> to_date('20051231','mmddyyyy') --Last day of the 4th qtr
20042 -> to_date('20040630','mmddyyyy') --Last day of the 2nd qtr
select (to_date('20051231','mmddyyyy')-to_date('20040630','mmddyyyy'))/90 from dual
gives me correct qtrs between them.
But does it work with all months [ Leap year, etc..]?
Can you provide me with a better solution?
Thanks,
June 14, 2006 - 12:42 pm UTC
convert yyyy qtr into a date (first day of the month of the quarter)
months_between() the two resulting dates
divide by 3.
don't use days, use months_between.
Can I covert using a function or ..
A reader, June 14, 2006 - 12:46 pm UTC
do i have to hard code first day of the month for each quarter?
like if qtr=1 then '0101'||year
elseif qtr=2 then '0104'||year ..etc
can i use to_date('20054','yyyyQ') from dual..
something like that.
Thanks,
June 15, 2006 - 8:05 am UTC
'math' works well - multiply :)
q cannot be in an input format
ops$tkyte@ORA10GR2> select q, 3*q-2 month
2 from (select level q from dual connect by level <= 4)
3 /
Q MONTH
---------- ----------
1 1
2 4
3 7
4 10
Neat trick to get first Month from Quarter, But.
A reader, June 15, 2006 - 9:56 am UTC
It doesn't help if i have to get first day of the month. Does it?
I can use last_day() function to get last day of that month.but, I do not know if there is first_day() function. So I still have to hardcode day.
Sorry If I did not understand your response properly..
June 15, 2006 - 4:44 pm UTC
eh? first day is always "1"
hard code "1"
First day of month
Thijs, June 16, 2006 - 7:49 am UTC
Given an arbitrary date in a month, you can get the first day of the month with TRUNC(datevar, 'MON').
June 16, 2006 - 7:07 pm UTC
not needed here, just need the number "1"
Here is the example in full I guess:
ops$tkyte@ORA10GR2> variable d1 varchar2(10);
ops$tkyte@ORA10GR2> variable d2 varchar2(10);
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec :d1 := '20054'; :d2 := '20042'
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select to_date( substr(:d1,1,4) || to_char( substr(:d1,5)*3-2, 'fm00' ) || '01', 'yyyymmdd' ) d1,
2 to_date( substr(:d2,1,4) || to_char( substr(:d2,5)*3-2, 'fm00' ) || '01', 'yyyymmdd' ) d2
3 from dual;
D1 D2
--------- ---------
01-OCT-05 01-APR-04
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select months_between( d1, d2 )/3
2 from (
3 select to_date( substr(:d1,1,4) || to_char( substr(:d1,5)*3-2, 'fm00' ) || '01', 'yyyymmdd' ) d1,
4 to_date( substr(:d2,1,4) || to_char( substr(:d2,5)*3-2, 'fm00' ) || '01', 'yyyymmdd' ) d2
5 from dual
6 );
MONTHS_BETWEEN(D1,D2)/3
-----------------------
6
That's what I was looking for :-)
A reader, June 19, 2006 - 9:39 am UTC
Thanks Tom, You nailed it. That exactly what i was trying to do. Thanks a bunch.
Also, I should thank "Thijs" from harleem, for mentioning 'MON' to get first day of the month. I did not know it :-).
Thank you both.