re-formatting a date

[meridian99]

Hello all,
I have a date formatting issue and would ask for some sql help. I have a oracle field that is a varchar2 in a table that stores a birth_date value as text in the format of mm/dd/yy as text. I would like to re-format the value into mm/dd/yyyy to include the century and leave it as text. Is there a sql scripting that would perform this? Or do I have to change the table field to a date type.

thanks

 

[SantaMufasa]

Meridian,

First, if you are working with dates in Oracle, it is ALWAYS best to use datatype "DATE" to store your information. Oracle has such remarkable date-handling functions, that you sacrifice much by storing dates in VARCHAR2.

If you must leave "birth_date" as VARCHAR2, then the first thing to confirm is that the VARCHAR2 column maximum is large enough to accommodate the addition of two-character centuries:

desc <table_name>

Name                    Null?    Type
----------------------- -------- -----------
...
BIRTH_DATE                       VARCHAR2(8)
...

The above shows that BIRTH_DATE is not large enough to accommodate the addition of century. Therefore, you must execute this change:

alter table <table_name> modify birth_date varchar2(10);

Table altered.

desc <table_name>
Name                    Null?    Type
----------------------- -------- ------------
...
BIRTH_DATE                       VARCHAR2(10)
...

To confirm contents of the table:

select birth_date from <table_name>;

10/06/04
03/08/90
06/17/91
04/07/90
03/04/90

5 rows selected.

To confirm the correctness of your data change before actually making the UPDATEs to each row:

select to_char(to_date(birth_date,'mm/dd/rr'),'mm/dd/yyyy') birth_date
from <table_name>;

10/06/2004
03/08/1990
06/17/1991
04/07/1990
03/04/1990

5 rows selected.

Then, to actually implement the changes:

update <table_name>
set birth_date = to_char(to_date(birth_date,'mm/dd/rr'),'mm/dd/yyyy');

5 rows updated.

To confirm the changes before you COMMIT them:

select birth_date from meridian;

10/06/2004
03/08/1990
06/17/1991
04/07/1990
03/04/1990

5 rows selected.

If all looks good, then:

COMMIT;

Commit complete.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:59 (06Oct04) UTC (aka "GMT" and "Zulu"), 12:59 (06Oct04) Mountain Time)

 

[meridian99]

Hi Mufasa, thanks for the great help. The field size is ok. But when I 'confirm the correctness of the change'
using

select to_char(to_date(birth_date,'mm/dd/rr'),'mm/dd/yyyy') birth_date
from <table_name>;

I noticed that all years are prefixed with '20' as in
old date = 06/28/64
now shows as 06/28/2064

 

[SantaMufasa]

Meridian,

Are you absolutely, positively certain that you used the '...rr...' mask in the to_date(birth_date,'mm/dd/rr') mask? If you did, then something is definitely bogus.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:27 (06Oct04) UTC (aka "GMT" and "Zulu"), 13:27 (06Oct04) Mountain Time)

 

[meridian99]

Hi Mufasa, your right! my mistake. Works perfect now.
thanks again