R: Trivia question re TO_DATE(SYSDATE)

The full explanation can be this:
TO_DATE(SYSDATE) works because of the internal conversions Oracle makes for you.
This means tha TO_DATE(SYSDATE) is a (useless and dangerous) shortcut for: TO_DATE(TO_CHAR(SYSDATE, <nls_date_format>), <nls_date_format>); In the inner conversion you can loose precision according to the date format. When you reconvert in date the missing precision is filled in the default way that for time is 00.00.00.

What happens if you have not SYSDATE but a date variable (say MY_DATE)containing the internal oracle date representation of '15 October 2001' with nls_date_format = 'ddmmyy'?
Simple but terrible: TO_DATE(MY_DATE) = the internal oracle date representation of '15 October 1901' ... and it's not a MILLENIUM BUG!!!!! Regards,
Riccardo

> Ah, that makes sense... I'm guessing that the reason the time is only
> truncated occasionally is that sometimes NLS_DATE_FORMAT is set to the
> default 'DD-MON-YY', and sometimes it's set to 'DD-MON-YY HH:MM:SS' (or
> some format like that - I'm just guessing). In this application that's
> quite possible...
>
> I think it's time I hit the manuals (this is my first serious Oracle
> adventure). Thanks for the explanation!
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Oct 14 1999 - 10:48:13 CDT