Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: sysdate - yesterday's date and time

    I need to compare a timestamp with yesterday's date at 2am and am having difficulty formulating the sysdate format.
    Can you help please

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I have difficulties in understanding what you need. "Yesterday" was, obviously, "before today" (but that's probably not a comparison you are looking for).

    Topic title contains a hyphen (-) sign. Is it a "minus" (i.e. you want to subtract two date values)? If so, here you are:
    Code:
    SQL> select sysdate                now,
      2    trunc(sysdate - 1) + 2/24   yesterday_2_am,
      3    --
      4    sysdate - (trunc(sysdate - 1) + 2/24) num_of_days  --> difference
      5  from dual;
    
    NOW                 YESTERDAY_2_AM      NUM_OF_DAYS
    ------------------- ------------------- -----------
    09.02.2012 14:50:09 08.02.2012 02:00:00  1,53482639
    
    SQL>
    Result is NUMBER OF DAYS between two dates. If you need to recalculate it to another format (days, hours, minutes), do some arithmetic (1 day = 24 hours, 1 hour = 60 minutes, etc.).

  3. #3
    Join Date
    Feb 2012
    Posts
    2
    thanks for your reply.
    That has helped resolve the problem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •