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
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')
Thanks for your help
have used the below statement while creating my view from table
) 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
Its working...there could be some syntax error..check it...
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,