This discussion is archived
6 Replies Latest reply: Feb 2, 2013 11:51 PM by jeneesh RSS

Logic to compare date with standard timestamp

976208 Explorer
Currently Being Moderated
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.
  • 1. Re: Logic to compare date with standard timestamp
    NSK2KSN Journeyer
    Currently Being Moderated
    if you want to implement with out timestamp, use trunc(sysdate) which will trunc the timestamp
    select sysdate, trunc(sysdate) from dual;
  • 2. Re: Logic to compare date with standard timestamp
    jeneesh Guru
    Currently Being Moderated
    UPDATE_DATE < ADD_MONTHS(trunc(sysdate)+1,-3);
  • 3. Re: Logic to compare date with standard timestamp
    976208 Explorer
    Currently Being Moderated
    Thanks for your reply.

    Your query is not meeting my requirement.
    It just adding one day after it's adding months.

    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.
  • 4. Re: Logic to compare date with standard timestamp
    jeneesh Guru
    Currently Being Moderated
    973205 wrote:
    Thanks for your reply.

    Your query is not meeting my requirement.
    It just adding one day after it's adding months.

    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.
    What you are trying to do is to concatenate a character string to a DATE value - which will not work and is not logical also..
    This can be achieved as below - minus 1 second from trunc(sysdate+1)
    select trunc(sysdate+1)-(1/(24*60*60)) dt
    from dual;
    
    DT                   
    ----------------------
    03-Feb-2013 23:59:59   
    Your requirement is to filter as UPDATE_DATE<=ADD_MONTHS(sysdate||' '||'23:59:59',-3);

    This can be done as
    UPDATE_DATE<=ADD_MONTHS(trunc(sysdate+1)-(1/(24*60*60)),-3);
    The same thing can be achieved by replacing "<=" with "<" as
    UPDATE_DATE<ADD_MONTHS(trunc(sysdate+1),-3);
    Edited by: jeneesh on Feb 3, 2013 12:39 PM
  • 5. Re: Logic to compare date with standard timestamp
    sudher Newbie
    Currently Being Moderated
    Hi,

    The solutions given by Jeneesh is wonderful and generic which should be recommended.

    You could try also the following one, if you demand hard coding of '23:59:59' timestamp in your query..
    UPDATE_DATE <= add_months(To_Date(To_Char(Trunc(Sysdate), 'DD-MON-YYYY') || ' 23:59:59', 'DD-MON-YYYY HH24:MI:SS'),-3 );
    Hope the above could be useful.

    Regards,
    Sudher.
  • 6. Re: Logic to compare date with standard timestamp
    jeneesh Guru
    Currently Being Moderated
    sudher wrote:

    You could try also the following one, if you demand hard coding of '23:59:59' timestamp in your query..
    UPDATE_DATE <= add_months(To_Date(To_Char(Trunc(Sysdate), 'DD-MON-YYYY') || ' 23:59:59', 'DD-MON-YYYY HH24:MI:SS'),-3 );
    This can be more easily achieved by using INTERVAL data type..
    select Trunc(Sysdate) + interval '23:59:59' hour to second dt
    from dual;
    
    DT                   
    ----------------------
    03-Feb-2013 23:59:59 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points