sysdate - yesterday's date and time

halstead · #1 (**permalink**) 02-09-12, 07:42

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

Littlefoot · #2 (**permalink**) 02-09-12, 08:51

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.).

halstead · #3 (**permalink**) 02-09-12, 09:10

thanks for your reply.
That has helped resolve the problem

Internet Services

Web Development

Digital Marketing

Consumer Tech