All Forums General
raghu01 2 posts Joined 03/15
05 Mar 2015
Date format DD-MON-YYYY to YYYY-MM-DD

Hi I have the source table coulumn as  varchar 
need to create a view for that table 
the view should display the string eg :  '20-JAN-1989' as ;1989-01-20 00:00:00'
Need your help on this 

dnoeth 4628 posts Joined 11/04
05 Mar 2015

Of course VarChar is the wrong datatype for storing a date, better cast it once during load and store it as a date.
 
You could cast the varchar to a date and then back to a varchar:

TO_CHAR(TO_DATE(col, 'dd-mon-yyyy'), 'yyyy-mm-dd hh24:mi:ss')

 

Dieter

raghu01 2 posts Joined 03/15
07 Mar 2015

Hi Dnoeth,

Thanks for your help 
 
have used the below statement while creating my view from table  
TO_CHAR(TO_DATE(col, 'dd-mon-yyyy'), 'yyyy-mm-dd hh24:mi:ss') as col
 
is showing error like FUNCTION 'TO_DATE' is called with an invalid numberor type of parameters
 
can please help me , the view should have col as date yyyy-mm-dd

dnoeth 4628 posts Joined 11/04
09 Mar 2015

What's the actual data & datatype of col?

Dieter

yuvaevergreen 93 posts Joined 07/09
11 Mar 2015

Its working...there could be some syntax error..check it...
SEL 
CAST('01 APR 1999' AS VARCHAR(15)) AS T1,
TO_CHAR(TO_DATE(T1,'DD MON YYYY') ,'YYYY-MM-DD HH:MI:SS')
 
Thanks and regards,
Yuva

You must sign in to leave a comment.