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.
:confused:

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)

Quote:

---

Originally Posted by gandolf989

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

---

+1/(24*60), surely?

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. :o ;)

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

---

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

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.

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

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
)

:D

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
)

Quote:

---

Originally Posted by wbible

:D

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;

---

the original poster requirement is:

post date/time before midday then date ||'00:00:01'
after midday date+1 || '00: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)

Or ultimately better for performance is to skip the completely unnecessary SELECT and simply assign a date-type variable the value ROUND(sysdate)+1/(24*60). Why drag down your database with a select when it's not even needed ?!