Home » SQL & PL/SQL » SQL & PL/SQL » Date Format
Date Format [message #196505] Thu, 05 October 2006 12:18 Go to next message
punu77
Messages: 20
Registered: July 2005
Junior Member
Hi

Can someone help me please.

I have one column name abc which have different dateslike

abc
9803 yymm
19980430 yyyymmdd

when I run following query
DECODE(NVL(abc,0),0,NULL,-1,NULL,TO_DATE(LPAD(abc,4,'0')||'01','rrmmdd')),

when abc is 9803 it works fine. but when abc is 19980430 it's gives error that not a valid month.

Can you give me some suggestion How I can get full date in one query with both conditions.
Re: Date Format [message #196509 is a reply to message #196505] Thu, 05 October 2006 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
stop using VARCHAR2 datatypes to store dates values.
Fire the data architect & redesign the application.
Re: Date Format [message #196510 is a reply to message #196505] Thu, 05 October 2006 12:38 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Agree with anacedent, but I realize we sometimes inherit these systems.

select case length(abc) when 4 then to_date(abc,'rrmm')
	when 8 then to_date(abc,'rrrrmmdd') end Day from table1;
Re: Date Format [message #196512 is a reply to message #196505] Thu, 05 October 2006 12:45 Go to previous message
punu77
Messages: 20
Registered: July 2005
Junior Member
thank you
Previous Topic: Recordset Paging Example
Next Topic: schema documentation
Goto Forum:
  


Current Time: Mon Dec 05 14:50:53 CST 2016

Total time taken to generate the page: 0.04866 seconds