Warning: (mis)-use of TO_DATE instead of TRUNC

From: <esiyuri_at_my-dejanews.com>
Date: Wed, 29 Jul 1998 16:01:55 GMT
Message-ID: <6pnh1k$scp$1@nnrp1.dejanews.com>

I have recently started working at a new site and was a little bit surprised to find that a common practice here is to use TO_DATE(SYSDATE) instead of TRUNC (SYSDATE) when attempting to strip the time portion from a date.

Using TO_DATE(SYSDATE) actually produces the desired result, but I have convinced the developers to change to using TRUNC() anyway. However, I was initially a little confused as to why it worked at all.

I now think that the reason it works is that since TO_DATE() expects a character input, the database automaticly converts SYSDATE to a string, using the current NLS_DATE_FORMAT of 'DD-MON-YYYY'. This gives a date, without the time portion. The following tests back this up:

SQL> select to_char(to_date(sysdate),'DD-MON-YYYY HH24:MI') from dual;

29-JUL-1998 00:00 (TIME STRIPPED OFF) SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI'; SQL> select to_char(to_date(sysdate),'DD-MON-YYYY HH24:MI') from dual;

29-JUL-1998 16:41 (TIME NOT STRIPPED OFF) SQL> alter session set NLS_DATE_FORMAT='MON-YYYY'; SQL> select to_char(to_date(sysdate),'DD-MON-YYYY HH24:MI') from dual;

01-JUL-1998 00:00 (OOPS - DATE CHANGED!!!)

This shows that the way TO_DATE() strips off the time portion is just a side effect of converting the date using the current NLS_DATE_FORMAT setting. If the format includes the time, the time portion is NOT stripped off. If the format does not include the date-of-month (DD) then THAT is stripped off. This may be a useful trick, but is not a good alternative to using TRUNC() if you just want to strip off the time portion.

Just thought I'd share this with you guys. Please let me know if you have any coments.

Regards
Yuri McPhedran

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Jul 29 1998 - 11:01:55 CDT