How do I make a SQL statement that will update an Oracle varchar2 field with a 2-digit date, to a 4-digit date

Question

I have a varchar2(20) field that has date in it. When I TO_DATE them, I get 2098 instead of 1998. They are stored as character strings like '24-JAN-98' and '13-MAR-03'. I need to change all these records so they have a 4-digit date, by entering a '19' in front of anything before 2000 and a '20' for everything after. So I'm thinking there should be some sort of SQL (or PL/SQL) where I could use instr, replace, or some other function that a beginning Oracle developer - like me - hasn't mastered yet. I have about 250,000 records that I need to do this to.

Answer 1

how about using this.

alter session set nls_date_format = 'DD-MON-RRRR';
update ur_table_name a
set the_varchar2_col = ( select to_char( to_date( the_varchar2_col ) )
from ur_table_name b
where a.primarykey = b.primarykey);
--confirm you have the right data and then
commit;
alter session set nls_date_format = 'your_previous_nls_date_format_here';

Be sure to read the RR Date format for long term use of this code, way after we have retired :) ;)

Answer 2

Wow, it has been a long time since I've done any SQL. I'll use the following and you replace with your table and field: mytable, datefield

I'm assuming that all of your dates have the same 9 character format: dd-mmm-yy.

update mytable
set datefield = substr(datefield,1,7)||
'19'||
substr(datefield,8,2)
where
substr(datefield,8,2) != '00'
;

Then repeat this putting in "20" for the "19", and
changing the != to =.
Ok, I had to put this on separate lines so it would show
but the whole thing is usually on one line although as
shown it should work.

This should take the first seven characters, concatenate a '19' with them and concatenate the last two characters with that.

As I said, it has been a long time. I do believe the syntax for the substr command is (field, start position, length) If this is wrong you should be able to correct it easily enough. By the way, this isn't the only way to do this, but I think it is the simplest. Have fun. If you problems reading the code, you can contact me at [e-mail]. I worked with the Oracle products for over 20 years, the last 11 as an Oracle employee and will be happy to help you if I can.

Answer 3

Here are 2 SQL statements that should work.
Keep in mind, that any dates where the year is after 1990 will have "19" put in front, and those that have a value less than "90", will get "20" put in front of the year.

UPDATE DATE_FIELD
SET DATE_FIELD = SUBSTR(DATE_FIELD,1,7) || '19' || SUBSTR(DATE_FIELD,8,2)
WHERE SUBSTR(DATE_FIELD,7,2) >= '00' AND SUBSTR(DATE_FIELD,7,2) <= '40' AND LENGTH(DATE_FIELD) = 9

UPDATE DATE_FIELD
SET DATE_FIELD = SUBSTR(DATE_FIELD,1,7) || '20' || SUBSTR(DATE_FIELD,8,2)
WHERE SUBSTR(DATE_FIELD,7,2) > '40' AND SUBSTR(DATE_FIELD,7,2) <= '99' AND LENGTH(DATE_FIELD) = 9


Also, you might want to double-check that it's working right by
using a select statement first, like this:
SELECT SUBSTR(DATE_FIELD,1,7) || '20' || SUBSTR(DATE_FIELD,8,2)
WHERE SUBSTR(DATE_FIELD,7,2) > '40' AND SUBSTR(DATE_FIELD,7,2) <= '99' AND LENGTH(DATE_FIELD) = 9


NOTE: The double-pipe symbol may have to be replaced by an ampersang (&)!