PL/SQL date calculation

[wbible]

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.

[gandolf989]

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)

[slimdave]

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

+1/(24*60), surely?

[gandolf989]

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

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

[DaPi]

Nearest midnight: won't TRUNC(sysdate + 0.5)+1/(24*60) do that?

[wbible]

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.

[gamyers]

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

[culonbu]

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
)

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

[gandolf989]

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???

SELECT ROUND(sysdate)+1/(24*60)
from dual;