| TRUNC(SYSDATE) [message #498965] |
Sat, 12 March 2011 05:15  |
muktha_22
Messages: 527
Registered: December 2009
|
Senior Member |
|
|
Hi All,
Can any one give me the reason to use the the trunc function along with the SYSDATE.
Eg:- TRUNC(SYSDATE).
Why we need to set the current date into midnight?
Where we can use this method?
Regards:
Muktha
|
|
|
|
| Re: TRUNC(SYSDATE) [message #498970 is a reply to message #498965] |
Sat, 12 March 2011 05:48  |
 |
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
|
Senior Member |
|
|
Quote:Why we need to set the current date into midnight? I would say it is not the need.
When you don't really need / consider the time, then you can use trunc.
Usually when we don't care about time, we can store the date with zero time stamp and between can be used easily without applying much logic.
For example, we have xxx column as '09-03-2011 00:00:00' and '10-03-2011 10:20:00' and want to retrieve the data from 1st to 10 March,
then between can be used without using trunc. trunc at selection may not use index on a direct column.
xxx between to_date('01-03-2011,'DD-MM-YYYY') and to_date('10-03-2011,'DD-MM-YYYY')
won't result the second row with '10-03-2011 10:20:00' and need to use < '11-03-2011' or adding 23:59:59, etc.
If we would have used trunc at the time of insertion, the second row would have inserted with zero time stamp '10-03-2011 00:00:00' and above sql would have resulted.
By
Vamsi
|
|
|
|
| Re: TRUNC(SYSDATE) [message #498976 is a reply to message #498970] |
Sat, 12 March 2011 07:14 |
cookiemonster
Messages: 13968
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
One obvious use of trunc(sysdate) is to get records that were created today:
SELECT *
FROM table
WHERE created_date > trunc(sysdate)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
