CODEWITHSUNDEEP
oracle-database
user3411037
user3411037

Reputation: 31

how to convert a string to a date format and then convert it back into a date in oracle

I need to convert a string that is of format (YYYY-MM-DD HH24:MI:SS) to date format(DD-MON-RR). After this I have to again convert it back to a string format(MM/DD/YYYY).

I have done this using the below Query

SELECT to_char(to_date('string to be converted','YYYY-MM-DD HH24:MI:SS'),'MM/DD/YYYY') 
FROM table_name

But I am getting an error like

ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0" *Cause: Illegal year entered *Action: Input year in the specified range

Can any one please suggest ideas for this.

Thanking you in advance

Upvotes: 0

Views: 428

Answers (1)

Emmanuel
Emmanuel

Reputation: 14209

Check your table values, because if they respected your format your query would be ok. For instance,

SELECT to_char(to_date('2014-03-12 15:19:33','YYYY-MM-DD HH24:MI:SS'),'MM/DD/YYYY')
FROM dual

gives 03/12/2014, as expected.

EDIT : I could not reproduce your error (was getting ORA-01861 instead), so I tried naively to run

SELECT to_char(to_date('string to be converted','YYYY-MM-DD HH24:MI:SS'),'MM/DD/YYYY')
FROM dual

and I do get the same error ORA-01841 as yours: this is because you are trying to convert 'string to be converted', which is not respecting the format ! You should take an Oracle field, something like table_name.date_field.

Upvotes: 1

Related Questions