Home » SQL & PL/SQL » SQL & PL/SQL » Logic to compare date with standard timestamp (Oracle 11g)
Logic to compare date with standard timestamp [message #576306] Sun, 03 February 2013 00:10 Go to next message
ajaykumarkona
Messages: 399
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 #576307 is a reply to message #576306] Sun, 03 February 2013 00:17 Go to previous messageGo to next message
BlackSwan
Messages: 21936
Registered: January 2009
Senior Member
I have no evidence that you are trainable.
Convince me that I an wrong.
Re: Logic to compare date with standard timestamp [message #576308 is a reply to message #576307] Sun, 03 February 2013 01:04 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
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'
after that I want to add months.

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 Go to previous message
Michel Cadot
Messages: 57599
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
Previous Topic: Cursor
Next Topic: need to different columns
Goto Forum:
  


Current Time: Wed Apr 16 02:59:58 CDT 2014

Total time taken to generate the page: 0.06708 seconds