OraFAQ Forum: SQL & PL/SQL » Convert year/quarter number to date format

Home » SQL & PL/SQL » SQL & PL/SQL » Convert year/quarter number to date format (Oracle 10G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Convert year/quarter number to date format [message #478040]

Tue, 05 October 2010 16:30

uicmxz
Messages: 48
Registered: July 2006

Member

I have year/quarter number field (200903 3-rd quarters of 2009) and I need to convert to data format. How can I do it? Confused

Report message to a moderator

Re: Convert year/quarter number to date format [message #478041 is a reply to message #478040]

Tue, 05 October 2010 17:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have year/quarter number field (200903 3-rd quarters of 2009) and I need to convert to data format.
What is "data format"?
Into what should 200903 be converted?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Convert year/quarter number to date format [message #478044 is a reply to message #478041]

Tue, 05 October 2010 17:30

uicmxz
Messages: 48
Registered: July 2006

Member

200903 is number that represent year and quarter need to be converted to date format 'MM/DD/YYYY'. I need to compare the year/quarter with effective and termination date that are in date format.

Report message to a moderator

Re: Convert year/quarter number to date format [message #478046 is a reply to message #478044]

Tue, 05 October 2010 19:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>200903 is number that represent year and quarter need to be converted to date format 'MM/DD/YYYY'.
Q3 is three months long so do we get to choose which date within Q3 gets returned.
It appears you need to write a custom FUNCTION to complete the desired conversion.

Report message to a moderator

Re: Convert year/quarter number to date format [message #478047 is a reply to message #478044]

Tue, 05 October 2010 19:17

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Code: [Select all] [Show/ hide]

with t as (select 200903 yq from dual)
select add_months(trunc(to_date(yq,'yyyymm'),'yyyy'),(mod(yq,100)-1)*3) from t
/

ADD_MONTH
---------
01-JUL-09

SQL>

SY.

Report message to a moderator

Re: Convert year/quarter number to date format [message #478048 is a reply to message #478047]

Tue, 05 October 2010 19:21

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Another solution:

Code: [Select all] [Show/ hide]

with t as (select 200903 yq from dual)
select to_date(trunc(yq/100)*100 + (mod(yq,100)-1)*3+1,'yyyymm') from t
/

TO_DATE(T
---------
01-JUL-09

SQL>

SY.

Report message to a moderator

Previous Topic:	regexp_replace (2 merged)
Next Topic:	utl_file Error in oracle 9i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 03 19:43:11 CDT 2025