Date manipulation

Good luck!

I would advise you to upgrade your SQL Developer first. Version 4.0.1 is quite old.

This is not a SQL Dev question, but more a SQL question, the SQL language, not the SQL Dev product. This might be a better space for this question: SQL & PL/SQL

19:00 hours yesterday is midnight today minus 5 hours. So this gives me 19:00 yesterday: TRUNC(sysdate)-5/24 and an example is here:

SQL> SELECT to_char(sysdate,'MM/DD HH24:MI') AS curr_time,

  2  to_char(trunc(sysdate)-5/24,'MM/DD HH24:MI') as yesterday_1900

  3  FROM dual;

CURR_TIME  YESTERDAY_1

----------- -----------

10/10 09:26 10/09 19:00

that works because there are 24 hours in a day and 5 hours is 5/24.

If you want 23:59 more than 19:00 yesterday, you just add that much of a day. In this case, there are 1440 minutes in day and i want to add 1/1440:

SQL> SELECT to_char(sysdate,'MM/DD HH24:MI') AS curr_time,

  2  to_char(TRUNC(sysdate)-5/24,'MM/DD HH24:MI') AS yesterday_1900,

  3  to_char(TRUNC(sysdate)-5/24+1-1/1440,'MM/DD HH24:MI') AS today_1859

  4  from dual;

CURR_TIME   YESTERDAY_1 TODAY_1859

----------- ----------- -----------

10/10 09:31 10/09 19:00 10/10 18:59

Cheers,
Brian

This query will be added in a report that get's refreshed on a daily basis but at different hours so can't use sysdate - 1.

So use 'TRUNC(sysdate) - 1'.

If you need more help mark this thread ANSWERED and repost the question in the sql and pl/sql forum.