Get systime at a specific time and date - Oracle SQL

Admin 1
Viewed 130

Is it possible to do something like this?

SELECT
    TO_CHAR(SYSDATE-1, 'MM-DD-YYYY 08:00:00') "Yesterday",

I'd like to get the sysdate from yesterday at 8am

oracle-sqldeveloper select date oracle sql
2 Answers

You would write this as:

trunc(sysdate - 1) + interval '8' hour

Or:

trunc(sysdate) - interval '16' hour

Or you can do date arithmetics with integer values rather than intervals:

trunc(sysdate) - 16/24
user_106767160
  • Truncate the sysdate. That will remove the time portion of the date.
  • Then add 8/24 (8 hours of the 24), 8/24 equals to 1/3 - 8 hours is a third of a day.
  • Then display this date value in the format DD-MON-YYYY HH24:MI:SS.

This all adds up to:

SELECT TO_CHAR(TRUNC(SYSDATE) - 1 + 1/3,'DD-MON-YYYY HH24:MI:SS') FROM DUAL;
user_41898140
Related