Oracle date to string conversion

Question

I have a string column COL1 when I am doing this  SELECT TO CHAR TO DATE COL1  dd-mon-yy     mm dd yyyy   FROM TABLE1   The data in COL1 is in dd-mon-yy  eg  27-11-89 and 89 is 1989 but the select returns it as 11 27 2089   I have to do an inner TO DATE because if I don t then I am getting an invalid number error  ORA-01722  invalid number    How can show 1989 instead of 2089  Please help

User · Answer

Another thing to notice is you are trying to convert a date in mm dd yyyy but if you have any plans of comparing this converted date to some other date then make sure to convert it in yyyy-mm-dd format only since to char literally converts it into a string and with any other format we will get undesired result  For any more explanation follow this   Comparing Dates in Oracle SQL

User · Answer

Try this  Oracle has this feature to distinguish the millennium years    As you mentioned  if your column is a varchar  then the below query will yield you 1989    select to date column name  dd mm rr   from table1   When the format rr is used in year  the following would be done by oracle   if rr- 00 to 49   ---  result will be 2000 - 2049   if rr- 50 to 99   ---  result will be 1950 - 1999

User · Answer

If your column is of type DATE  as you say   then you don t need to convert it into a string first  in fact you would convert it implicitly to a string first  then explicitly to a date and again explicitly to a string    SELECT TO CHAR COL1   mm dd yyyy   FROM TABLE1   The date format your seeing for your column is an artifact of the tool your using  TOAD  SQL Developer etc   and it s language settings

User · Answer

The data in COL1 is in dd-mon-yy   No it s not  A DATE column does not have any format  It is only converted  implicitely  to that representation by your SQL client when you display it   If COL1 is really a DATE column using to date   on it is useless because to date   converts a string to a DATE    You only need to char    nothing else   SELECT TO CHAR col1   mm dd yyyy    FROM TABLE1   What happens in your case is that calling to date   converts the DATE into a character value  applying the default NLS format  and then converting that back to a DATE  Due to this double implicit conversion some information is lost on the way     Edit  So you did make that big mistake to store a DATE in a character column  And that s why you get the problems now    The best  and to be honest  only sensible  solution is to convert that column to a DATE  Then you can convert the values to any rerpresentation that you want without worrying about implicit data type conversion   But most probably the answer is  I inherited this model  I have to cope with it   it always is  apparently no one ever is responsible for choosing the wrong datatype   then you need to use RR instead of YY   SELECT TO CHAR TO DATE COL1  dd-mm-rr     mm dd yyyy   FROM TABLE1   should do the trick  Note that I also changed mon to mm as your example is 27-11-89 which has a number for the month  not an  word   like NOV   For more details see the manual  http   docs oracle com cd B28359 01 server 111 b28286 sql elements004 htm SQLRF00215

[sql] Oracle date to string conversion

Examples related to sql

Examples related to oracle