Getting a specific time of day in Oracle

OracleHere’s a way to force an Oracle date calculation to find a specific time of day every time. This is useful when using the DBMS_JOB package for job scheduling jobs as it gives you full control over the time of day a job runs.

The Oracle date data type is accurate down to the second, but if you want to ignore this precision and get to a certain time you can use the TRUNC function to truncate the date down to just the day, then add as many hours or minutes as you want.

By default the TRUNC command will truncate a date and time to midnight on the start of that date, so to consistently get, for example, 2:00 pm, we can truncate the time and add 14 hours.

SQL> SELECT to_char(trunc(sysdate) + 14/24, 'MM/DD/YYYY HH:MI AM') FROM dual;

TO_CHAR(TRUNC(SYSDA
-------------------
02/13/2006 02:00 PM

Here we truncate to midnight and add 14/24 of a day (14 hours). This works on the idea that the default increment for math with oracle dates is 1 day, so if we add 14/24 days we’re adding 14 hours. You could also use the NUMTODSINTERVAL function as described here, but it makes the command longer.

Applying this to scheduled jobs

When scheduling jobs with the DBMS_JOB package you specify the interval with a string which evaluates to an Oracle date. The interval is evaluated every time a job runs to calculate the next time the job should run. Typically the interval is something like 'SYSDATE + 1' if you want the job to run once a day.

The interval is evaluated each time the job starts. If the job runs at 11:00 PM, the interval would evaluate to 11:00 pm the following day; however, if you force the job to run with the DBMS_JOB.RUN at 2:30 pm, the interval would be evaluated to 2:30 pm the next day and you have changed your job to run at 2:30 every day thereafter.

To make sure things always run at the same time we can apply the method above to the interval when we submit the job.

SQL> BEGIN
DBMS_JOB.CHANGE(JOB => 21, WHAT => NULL, NEXT_DATE => NULL, INTERVAL => 'TRUNC(SYSDATE + 1) + 23/24';
END;
/

This would update job number 21 to use the string 'TRUNC(SYSDATE + 1) + 23/24' to calculate the next time this job should run. In this case, we take SYSDATE, plus 1 day, truncate it to midnight of that day, and add 23 hours. The end result is, no matter what time the job is run the interval will evaluate to 11:00 pm the next day.

oracle, database, dba, database programming, database administration, sql, plsql, pl/sql

7 thoughts on “Getting a specific time of day in Oracle”

  1. SELECT TO_CHAR(TRUNC (‘5/5/2006 9:54:33 AM’), ‘MM/DD/YYYY’ ) FROM dual ;

    I want to truncate date using this command.This is not giving result.Please help me.thanks

  2. Sandeep, you have a couple things going wrong here. First, the date you’re working with will need to be converted into the Oracle date format with a TO_DATE first like this:

    TO_DATE('5/5/2006 9:54:33 AM', 'MM/DD/YYYY HH:MI:SS AM')

    Once you’ve done that you could just use a to_char to view just the month part like this:

    SELECT TO_CHAR(TO_DATE('5/5/2006 9:54:33 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'MM/DD/YYYY') FROM DUAL;

    05/05/2006

    Now if you want to do this with the TRUNC function you’ll want to apply it around the TO_DATE function.

    SELECT TO_CHAR(TRUNC(TO_DATE('5/5/2006 9:54:33 AM', 'MM/DD/YYYY HH:MI:SS AM')), 'MM/DD/YYYY HH:MI:SS AM') from dual;

    05/05/2006 12:00:00 AM

    Here we see that the trunc command doesn’t remove the hour/minute/second from the date, but rather it sets them to midnight.

    Let me know if this helps.

  3. If we wanted to truncate time and and add our time like 11:59:00 AM in to date. What should we do?

    Pls advise
    Thanks
    Rajeev

  4. We can get the day as Sunday, Monday etc by using
    SELECT to_char(sysdate,’day’) FROM dual

    But is it possible to get the day of date as number? That is Sunday as 1, monday as 2 etc….

    pls help…..
    Rgds,
    Rathish.

  5. Rathish,

    What you want to use is ‘D’ instead of ‘day’ like in the following example (which was run today, wednesday.)

    SQL> select to_char(sysdate, ‘D’) from dual;

    T

    4

    UNIX gurus should be careful since cron starts numbering Sunday as 0 and Oracle starts at 1.

Leave a Reply

Your email address will not be published. Required fields are marked *