NEVER do a to_Date on a date Re: Trivia question re TO_DATE(SYSDATE)

NEVER do a to_date on a date type, doing so will cause Oracle to genreate inconsistent results that may or may not be what you expect.

We had a program that was giving us wierd inconsistant results that were difficult to track down. Turns out it was doing a to_date on date

The Oracle 8 doc states

"Do not use the TO_DATE function with a DATE value for the char argument. The returned DATE value can have a different century value than the original char, depending on fmt or the default date format. "

to get rid of the time portion use trunc(sysdate)

In article <7tqn9l$ebi$1_at_nnrp1.deja.com>, laleonard_SpamBanana_AT_mindspring.com <laleonard_at_mindspring.com> wrote:
>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!
>
>
>In article <7tqh58$i7g$2_at_news.seed.net.tw>,
> "fumi" <fumi_at_tpts5.seed.net.tw> wrote:
>>
>> The parameter of TO_DATE() function is CHAR or VARCHAR2 datatype.
>> Oracle implicitly converts sysdate into a VARCHAR2 string using
>TO_CHAR()
>> function, so the expression Oracle actually performed is
>> TO_DATE(TO_CHAR(SYSDATE)).
>> The default NLS_DATE_FORMAT is 'DD-MON-YY', that's why you lose
>> the time part of SYSDATE.
>>
>> The correct usage is just SYSDATE, never use the detrimental
>> TO_DATE(SYSDATE).
>
Received on Wed Oct 13 1999 - 10:39:01 CDT