| Logic to compare date with standard timestamp [message #576306] |
Sun, 03 February 2013 00:10  |
ajaykumarkona
Messages: 264
Registered: August 2010
|
Senior Member |
|
|
Hi Experts,
I want to retrieve the data which is "n" moths old.
To compare that I want use only SYSDATE without timestamp.
I want to use standard timestamp '23:59:59' along with SYSDATE.
The condition should be as below.
UPDATE_DATE <= ADD_MONTHS(15/01/2013 23:59:59,-3)
UPDATE_DATE <= ADD_MOTHS(30/01/2013 23:59:59,-4)
UPDATE_DATE<=ADD_MONTHS(sysdate||' '||'23:59:59',-3);
Please help me how to implement it.
Thanks in advance.
|
|
|
|
|
|
| Re: Logic to compare date with standard timestamp [message #576308 is a reply to message #576307] |
Sun, 03 February 2013 01:04  |
ajaykumarkona
Messages: 264
Registered: August 2010
|
Senior Member |
|
|
TRUNC(sysdate) means it will truncate timestamp.
I want to use standard timestamp along with sysdate.
TRUNC(sysdate)||' '||'23:59:59'
I tried the below logic it's not working.
SELECT ADD_MONTHS(TRUNC(sysdate)||' '||'23:59:59',-3) FROM dual;
Please provide this logic.
Thanks.
|
|
|
|
| Re: Logic to compare date with standard timestamp [message #576309 is a reply to message #576308] |
Sun, 03 February 2013 01:27 |
 |
Michel Cadot
Messages: 54246
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:I tried the below logic it's not working.
Why?
Hint: TRUNC(sysdate) is a DATE; ' ' and '23:59:59' are strings, you cannot add apples and oranges, you cannot concatenate a date with a string. Be careful and strict in what you write.
TRUNC(sysdate)+1-1/86400
This is current day at 23:59:59, do you understand it?
Regards
Michel
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
