Date format DD-MON-YYYY to YYYY-MM-DD

General

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 

4 REPLIES
Junior Contributor

Re: Date format DD-MON-YYYY to YYYY-MM-DD

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')

Re: Date format DD-MON-YYYY to YYYY-MM-DD

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

Junior Contributor

Re: Date format DD-MON-YYYY to YYYY-MM-DD

What's the actual data & datatype of col?

Enthusiast

Re: Date format DD-MON-YYYY to YYYY-MM-DD

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