oracle-databasetimestampsysdate

Create TimeStamp with fixed Time Part

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

Solution

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