6 Replies Latest reply: Feb 3, 2013 1:51 AM by jeneesh RSS

    Logic to compare date with standard timestamp

    976208
      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
          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
            UPDATE_DATE < ADD_MONTHS(trunc(sysdate)+1,-3);
            • 3. Re: Logic to compare date with standard timestamp
              976208
              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
                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
                  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
                    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