Re: Y2k Server Testing, What's in a date?

Hi...

I'm certainly no expert, but in my ignorance I agree, I don't think it is as simple as changing the NLS date format in your parameter file because:

1. Dates such as date of birth will need to be handled differently from more commonly used examples of the date datatype.
2. Any date columns entered/modified via an SQL script using the old default date format of DD-MON-YY will be misinterpreted using the following type of SQL statement:

SELECT COUNT(*)
FROM tablename
WHERE datecolumn < '01-JAN-50'

would count all rows where dates have been entered in this fashion, which must be incorrect.

c. Any date columns entered/modified via an application that doesn't, or can't, use the DD-MON-RR date format or an equivilant will also create incorrect data. e.g. Forms 3.

This would not present a problem in the past as like is compared with like. But changing the default date format would mean default data entry would now become inconsistant with entry before the change.

We maintained the consistency of data using three stages

1. Ensuring all interfaing applications (in our case Forms 3) were upgraded or amended so that the DD-MON-YY default format wasn't used literally.
2. Changing the default NLS format mask to ensure all future data would default to the DD-MON-RRRR format.
3. Counting rows from tables where date columns < '01-JAN-50' to identify problem areas (but excluding specially handled dates like dates of birth)
4. Correcting those affected columns by means of an UPDATE statement converting them to the 'DD-MON-RR' or 'DD-MON-RRRR' format.

We have also changed the system time on our UNIX development box to rigourously test the theory. So far, so good!

All special date fields e.g. dates of birth, have been amended in our application so entry is via a four digit year ensring they aren't inciorrectly ambushed by the default format mask.

This may well be an over complicated method or even an incorrect one but it seems to have got the job done (fingers crossed!).

Any remarks/suggestions/bouts of laughter would be accepted in good spirit!

Regards,

Bagpuss. Received on Thu Oct 08 1998 - 12:39:37 CDT