PL/SQL date calculation
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: PL/SQL date calculation

  1. #1
    Join Date
    Nov 2006
    Posts
    3

    Question PL/SQL date calculation

    I'm a confessed PL/SQL neophyte here but I have a question as to best handle setting a Postdate to the Sysdate and time an interface job runs at night or morning to the following:

    The client wants the following scenario:

    Set the OHPost date to be the date as of the closest midnight to the time of execution. Eg. If the interface is run on 10/31/06 at 23:55,then set the OHPost to 11/1/06 00:01. If the interface is run on 11/1/06 at 00:55,then set the OHPost to 11/1/06 00:01.

    I interpret this as meaning if the sysdate & time is > 12:00 PM then set the OHPOSTDATE := SYSDATE +1 || 00:01 AM)
    -- ie. The next day at 12:00:01 AM.

    else

    set the OHPOSTDATE := SYSDATE || 00:01 AM)

    This is being selected from dual in a PL/SQL procedure.

    I appreciate any advice here.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    Truncating SYSDATE gives you 12:00 AM on the current date.
    TRUNC(SYSDATE)

    Adding 1 puts the date at midnight tonight for the current date.
    +1

    Adding this causes date date to be 1 minute later
    +(1/24*60)

    The final function would be as follows.
    TRUNC(SYSDATE) + 1 + (1/24*60)
    this space intentionally left blank

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by gandolf989
    Adding this causes date date to be 1 minute later
    +(1/24*60)
    +1/(24*60), surely?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    Quote Originally Posted by slimdave
    +1/(24*60), surely?
    I apologize for posting untested theories, and want to thank you for pointing out my folly.

    btw, 1440 is the number of minutes in a day.

    Code:
    SQL> SELECT TO_CHAR(TRUNC(SYSDATE) + 1441/1440, 'DD-MON-YYYY:HH24:MI:SS:SSSS') from dual;
    
    TO_CHAR(TRUNC(SYSDATE)+1441/1440,'
    ----------------------------------
    17-NOV-2006:00:01:00:0000
    
    Elapsed: 00:00:00.00
    this space intentionally left blank

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Nearest midnight: won't TRUNC(sysdate + 0.5)+1/(24*60) do that?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  6. #6
    Join Date
    Nov 2006
    Posts
    3
    Thanks all ofr the responses. I'll test it out. It's the test of checking for the current time being before or after 12 PM in the statement that needs to be tested. Thanks again for your suggestions.

  7. #7
    Join Date
    Feb 2005
    Posts
    158

    Folks, we have a ROUND function

    v_new_date := round(v_old_date);
    It works for dates as well as numbers.

  8. #8
    Join Date
    Dec 2000
    Posts
    126
    Try this


    select decode(trunc_date,round_date,trunc(sysdate)+1/24/60/60,round(sysdate)+1/24/60/60) new_date
    FROM
    (
    select to_char(round(sysdate),'dd-mon-yyyy hh24:mi:ss') round_date,
    to_char(trunc(sysdate),'dd-mon-yyyy hh24:mi:ss') trunc_date
    from dual
    )

  9. #9
    Join Date
    Nov 2006
    Posts
    3

    Thumbs up Resolved



    Thanks for the statement

    This worked.


    select decode(trunc_date,round_date,trunc(sysdate)+1/24/60/60,round(sysdate)+1/24/60/60) new_date
    FROM
    (
    select to_char(round(sysdate),'dd-mon-yyyy hh24:mi:ss') round_date,
    to_char(trunc(sysdate),'dd-mon-yyyy hh24:mi:ss') trunc_date
    from dual
    )

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    Quote Originally Posted by wbible


    Thanks for the statement

    This worked.


    select decode(trunc_date,round_date,trunc(sysdate)+1/24/60/60,round(sysdate)+1/24/60/60) new_date
    FROM
    (
    select to_char(round(sysdate),'dd-mon-yyyy hh24:mi:ss') round_date,
    to_char(trunc(sysdate),'dd-mon-yyyy hh24:mi:ss') trunc_date
    from dual
    )
    So that works better than this???

    Code:
    SELECT ROUND(sysdate)+1/(24*60)
    from dual;
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width