2

I have a string column that contains the below data as an example

10/20/2005 15:08:00 
11252011 15:22:40

I have created a temp date column that I will copy the contents into, drop the string column and rename the temp column to what the string one was.

However in my attempt to copy the data across to the new temp column I am getting the error:

ERROR
ORA-01861: literal does not match format string

Here is the SQL:

update mytable set MYDATE_TEMP = to_date(mystringcol, 'yyyy/mm/dd hh24:mi:ss')

Is there a way to copy the data across?

Jon
  • 38,814
  • 81
  • 233
  • 382
  • You have data that doesnt match the date format specified. Try finding this data and if it is valid, you may need to use either decode, case or replace functions... Otherwise you might want to write a function that takes the column as input and returns either the date as a string, or the date itself – Lock Apr 12 '12 at 14:40

2 Answers2

6

If those are the only 2 formats you need to handle try:

update mytable set MYDATE_TEMP = to_date(replace(mystringcol,'/',''),
                                         'mmddyyyy hh24:mi:ss')
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • 1
    If you have lots of formats, you may need a PL/SQL function that will take in the string, try a bunch of formats, and return a date. One way is a nested series of v_return := to_date (in_text, ; exception when others then v_return := . If you're doing this a lot, define a named exception for the ORA-1861. For a one-time data fix, I'd just use "when others" – Jim Hudson Apr 12 '12 at 14:36
  • doesn't like it, I guess it should be mmddyyyy hh24:mi:ss – Jon Apr 12 '12 at 14:58
0

For the first one you could use:

update mytable set MYDATE_TEMP = to_date(mystringcol, 'mm/dd/yyyy hh24:mi:ss');

For the second one:

update mytable set MYDATE_TEMP = to_date(mystringcol, 'mmddyyyy hh24:mi:ss');
sloth
  • 99,095
  • 21
  • 171
  • 219
shkelzen
  • 11
  • 1