Dates as strings… What? We are JDE–our dates are 6 digit numbers…

Houston, we have a problem.

I got some development done recently, and some of the date fields were incorrectly defined as strings.  I guess I cannot be too critical, as the data from the spreadsheet that was going to be loaded into the file was a string… But…  Well, read between the lines, it was unexpected.

Now we have 40+ DD items, 6 tables and an entire suite of code (including mobile applications) that have been coded on the basis that the dates are strings.  unfortunately, this is not going to work.

my two DD items are

I can find the tables that are using these with the following oracle SQL:

>select table_name, column_name from all_tab_columns where owner = 'CRPDTA' and (column_name like '__55RPDATE' or column_name like '__55SCDATE');

TABLE_NAME                     COLUMN_NAME                   
------------------------------ ------------------------------
F55ACCCH                       AM55SCDATE                    
F55ACCCH                       AM55RPDATE                    
F55ACCRC                       CA55SCDATE                    

So, these tables have 100,000+ rows of data, so I’m going to do a little  bit of cheating.

• I’m going to create a temp column at the end of the table as a date

Date in JDE is defined as NUMBER(6)  in oracle

alter table crpdta.F55accch add ( DATETEMP number(6)) ;

• I’m going to populate this column with the date value of the string

select am55scdate, to_date(am55scdate, 'YYYY-MM-DD'), to_number(concat('1',to_char(to_date(am55scdate, 'YYYY-MM-DD'),'YYDDD'))) from crpdta.f55accch where AM55SCDATE like '20%';

select am55scdate
--,to_date(am55scdate, 'YYYY-MM-DD')
--,to_number(concat('1',to_char(to_date(am55scdate, 'YYYY-MM-DD'),'YYDDD')))
from crpdta.f55accch where AM55SCDATE not like '20%';

select am55scdate
,to_date(am55scdate, 'DD/MM/YYYY')
,to_number(concat('1',to_char(to_date(am55scdate, 'DD/MM/YYYY'),'YYDDD')))
from crpdta.f55accch where AM55SCDATE not like '20%' and am55scdate > ' ';

update crpdta.F55accch set DATETEMP = to_number(concat('1',to_char(to_date(am55scdate, 'YYYY-MM-DD'),'YYDDD'))) where AM55SCDATE like '20%';
update crpdta.F55accch set DATETEMP = to_number(concat('1',to_char(to_date(am55scdate, 'DD/MM/YYYY'),'YYDDD'))) where AM55SCDATE like '1%';
commit;

• I’m going to clear the problem column

update crpdta.F55accch set am55scdate = '';
commit;

• I’m then going to change the data type of the problem column

alter table crpdta.F55accch modify am55scdate number(6);

• Finally copying the data from the temp column and dropping the temp column.

update crpdta.F55accch set am55scdate = datetemp;

alter table crpdta.F55ACCCH drop column datetemp;

Although I could do this a little more simply, at least the desc of the table will have the correct column sequence, it’s the little things that matter sometimes.