Hi George, With this solution you can only find Quarter number. but I also need Year of that quarter number. when you say to_char(date,'Q'), it might go next year or stay in current fiscal year depending on number of months we add to the date.
Thanks, Surendra -----Original Message----- Sent: Wednesday, July 02, 2003 12:56 PM To: Multiple recipients of list ORACLE-L You don't need CASE. try : to_char( date , 'Q') George > Hello ALL, > > I am trying to find quarter number from a given date . Here is the > description > > Our Financial year runs from July thru June. So, Given any date between > these dates I need to find 3 quarters(9 months) from it. > July -Sep -> 1st Quarter > Oct -Dec -> 2nd Quarter > Jan -Mar -> 3rd Quarter > Apr -Jun -> 4th Quarter > > I got this done using the following Select > > select decode(to_char(add_months(sysdate,9),'Q'), > '1','3', > '2','4', > '3','1', > '4','2', > '') > > Decode function is used to change calender quarter to our Quarter. > > But I am unable to find the Year for that quarter. > I was using Case statement to solve my problem,as below > > Select case when decode(to_char(add_months(sysdate,9),'Q'), > '1','3', > '2','4', > '3','1', > '4','2', > '') < 3 > then to_number(to_char(main_rec.termination_date,''YYYY''))+1 > else to_number(to_char(main_rec.termination_date,''YYYY'')) > > P.S The reason for <3 condition check in CASE Statement is, if a > sysdate+9months falls in next Financial year , I need to change Year > accordingly. > > But,this works only in SQL, in Procedures, i cannot do this using CASE > Statement > > Can anybody give some ideas on how to approach this?. I have to use this in > a cursor (not in the body of my procedure, so condition checking like "If > then else " after fetching year is not possible) > > Any help would be greatly appreciated. > > Thanks, > Surendra Tirumala > Database Administrator > Cabinet for Workforce Development > Commonwealth of Kentucky > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: George Oneata INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
