Update date with specific format if value of column is not in same format in Oracle sql

Question

I have a table like below. Here the value is string though it has date values

id  date_value
1   28-JUL-17
2   20/10/17
3   21-09-2017
4   04-AUG-2017
5   (null)
6   xxxx

Now, I need to update the values of date_value column only if the dates are NOT in the format of dd-MON-yy. Also the values in that column can be null or any other value other than date which should be ignored

Please help.

Sorry, i missed one part. The values which are in format dd-MM-yy should be updated to dd-MON-yy.

So till now I tried

update table_a set value = to_char(to_date(date_value, 'dd-MM-yy'), 'dd-MON-yy')
where date_value is not null
and date_value not like '%/%/%'
and date_value != 'xxxx'

I got SQL Error: ORA-01858 - "a non-numeric character was found where a numeric was expected"

I was able to fix this using below query

update table_a set value = to_char(to_date(date_value, 'dd/MM/yyyy'), 'dd-MON-yy') 
where regexp_like (date_value, '[0-9]{2}/[0-9]{2}/[0-9]{4}')

To which values do you want to update the matching rows? Also, why are you storing date information as text? — Tim Biegeleisen, Nov 02 '17 at 15:02
What have you tried so far? I could write a query for you, but I'm not quite sure it would be what you want until I see the query you are trying. — NH., Nov 02 '17 at 15:02

Gordon Linoff · Accepted Answer · 2017-11-02T15:14:25.900

0

Use regular expressions:

update t
   set date = . . .
   where not regexp_like(date, '[0-9]{2}-[A-Z]{3}-[0-9]{2}');

This checks for the overall format. If you want the specific months:

update t
   set date = . . .
   where not regexp_like(date, '[0-9]{2}-(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)-[0-9]{2}');

The . . . is for the new value you want to use.

edited Nov 02 '17 at 15:14

answered Nov 02 '17 at 15:06

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Nov 02 '17 at 15:09

You could use a CASE statement.

Something along the lines of this:

UPDATE table_name   
SET date_value=   CASE  
                        WHEN (date_value LIKE '%-%')  THEN TO_DATE(date_value)   
                        WHEN (date_value LIKE '%/%')  THEN TO_DATE(REPLACE(date_value,'/','-'))
                        ELSE NULL 
                        END

See this post: https://stackoverflow.com/a/5171127/7613110

And Documentation: https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/case_statement.htm

score 0 · Answer 3 · 2017-11-02T16:25:42.857

I think you need to create a second column in the table that is date format and allows null. Then use Oracle's to_date function to convert from your column to the new column in date format.

The problem is that you have to specify the current date format in order to use to_date, so you'll need some additional SQL to determine what the current format is, i.e. if the first two positions > 12 then that should be dd/mm/yy format, so you'll use to_date(date_value,'dd/mm/yy').

There may be leftovers that you have to convert manually, such as 12/04/07. You should not have miscellaneous text in your table, so eliminate anything that is not a date or null.

Edit: This will store the date in standard Oracle format of YYYY-MM-DD HH:MM:SS which you will need to reformat when displaying using to_char(new_date_field,'dd/mm/yyyy')<--or whatever format you want.

Update date with specific format if value of column is not in same format in Oracle sql

3 Answers3