| sysdate usage [message #8042] |
Tue, 22 July 2003 23:49  |
Rajeev Katyal
Messages: 55
Registered: April 2002
|
Member |
|
|
Small help required.
suppose i want to select all the rows from abc table where one colum(say join_date) is =current date -1
Then how will i be writting the query ?
select * from abc where jon_dt=sysdate-1?
is it the right way ?
|
|
|
|
| Re: sysdate usage [message #8043 is a reply to message #8042] |
Wed, 23 July 2003 00:24  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Yes it is. Date calculations are done in units of days, so yesterday = SYSDATE-1. Be aware that a time portion (although not always visible) is always included. If not specified, it defaults to 00H00'00''.
To select all records entered yesterday in my_table (assuming that this table has an audit column containing the time a record was entered), you can do either:
SELECT something
FROM my_table
WHERE TRUNC(audit_column) = TRUNC(SYSDATE-1);
Or
SELECT something
FROM my_table
WHERE audit_column BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)-(1/24/60/60).
The second select can be more performant if you have an index on audit_column. The TRUNC function sets the time portion to 00H00'00''.
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
