Create TimeStamp with fixed Time Part

Question

What's the best way to get a timestamp that consists of the actual date but a fixed time part in oracle.

e.g.Today and always 09:00:00

2020-10-20 09:00:00

in MSSQL I would use FORMAT(GETDATE(),'yyyy-MM-dd 09:00:00')

Answer 1

Assuming you want a date rather than a varchar2, I'd use

trunc(sysdate) + interval '9' hour

trunc(sysdate) returns today at midnight and then interval '9' hour adds 9 hours to give you 9am. You can also add fractions of a day to a date so you could say

trunc(sysdate) + 9/24

I tend to find the interval notation more self-explanatory particularly if you're coming from a non-Oracle background.

Answer 2

You can use something like this:

SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD';

Session altered.

SQL> set head off

SQL> select sysdate||' 09:00:00' from dual;

2020-10-19 09:00:00

Hope this is what you were looking for :)