01-14-05, 11:11 #1Registered User
- Join Date
- Oct 2003
to shut this up once for all,
using trunc(any_date) won't get the lines where the date has hour information too ?
I've just queried some rows here using
AND date_column = to_date(any_date, any_format)
and it returned some rows with the column showing hour information and some not.
As I'm always listening to everybody say that to get date with hours you must use the "trunc" function on that date...
This seems strange.
(P.S.: and using trunc in an indexed column disables this index ?)
01-14-05, 11:33 #2Moderator.
Provided Answers: 1
- Join Date
- Sep 2002
You need to think about what an Oracle DATE value is. It always contains a date (year, month, day) and it always contains a time (hour, minute, second). It is a convention that if we want "just" the date then we set the hours, minutes and seconds to 0 - which is what the TRUNC function does:
SQL> select to_char(sysdate,'YYYY,MM,DD,HH24,MI,SS') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2005,01,14,16,27,24 SQL> select to_char(trunc(sysdate),'YYYY,MM,DD,HH24,MI,SS') from dual; TO_CHAR(TRUNC(SYSDA ------------------- 2005,01,14,00,00,00
WHERE datecol = TRUNC(SYSDATE)
WHERE TRUNC(datecol) = TRUNC(SYSDATE) WHERE datecol BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE)+1-1/24/60/60 WHERE datecol >= TRUNC(SYSDATE) AND datecol < TRUNC(SYSDATE)+1Tony Andrews