Re: update date filed in tables?

For what its worth, I agree with Arjan's assertion that it is bad practice to depend upon default date formatting. If this code was to go into some regularly scheduled production process, I would emphasize the need to specify the conversion formatting. Since this will probably only be done once this millennium, and that one time fairly soon, I simply didn't consider the issue important. If the default date formatting IS changed between now and when Yongge gets around to updating his database, I suspect Yongge will notice that no rows get updated.

Lets try this again.

Dates that convert to the string '31-dec-99' are somehow important to Yongge (OK, Arjan, dates that to_char(thrudate, 'dd-mon-yy') are somehow important to Yongge). However they got there, the question now is to change them to the date '31-dec-2099'.

Please note. We don't know what century the dates currently in the table are from. They could be 31-dec-99, 31-dec-1999, or, my personal favorite, 31-dec-9999. And we don't know what time. Could be morning, noon, or night. All we know is that, when converted through what appears to be the default date formatting, they came out '31-dec-99'. And we want them to become '31-dec-2099'.

The suggestion I am making is to convert the date to a character string, and then compare the two character strings. The alternative is to take the character string '31-dec-99', convert it to a date, and try to figure out which edited thrudate values might be equal to that date.

Arjan's suggestion to use the where clause:

    where trunc(thrudate) = to_date ('31-dec-1999', 'dd-mon-yyyy') is a good start, and by adding perhaps 99 more

       or trunc(thrudate) = to_date ('31-dec-cc99', 'dd-mon-yyyy') we get pretty close to the same place.

To say this in yet a different way, Yongge's assertion that the sql clause "where thrudate = to_date ('31-dec-99', 'dd-mon-yy') " does not exactly match his/her stated goal of updating thrudates with the formatted value of '31-dec-99'. The specific gaps I identified could be fixed by using a character-based comparison instead of using a date-based comparison.

If we need to beat this horse some more, please let me know.

Regarding the concern about not using an index if one is available. Whatever update statement Yongge uses, Oracle will PROBABLY not use an index. If "most of the values" are the same, Oracle (should) choose a full table scan when doing the update - even if an index is available.

Patrick Suppes

Arjan van Bentem wrote:

> Patrick Suppes wrote
> > The to_date ('31-dec-99', 'dd-mon-yy') syntax overrides the default
> > default conversion formatting. To_char(thrudate) = '31-dec-99'
> > more precisely matches the default
>
> Ooops! It is very, very bad practice to rely on the default date formats! The
> syntax Yongge used is certainly to be preferred (although using YYYY would
> have been better); yours simply should be avoided, unless you explicitly set
> NLS_DATE_FORMAT yourself (although even then there is no reason at all why
> you'd convert without specifying the date maskt). I hope Lucent has not hired
> "Database Technologies" for Y2k specific projects...
>
> > mine requires a conversion for each row
>
> ... and disables the use of the index that might exist on thrudate.
>
> Arjan.
Received on Fri Apr 23 1999 - 18:11:03 CDT