PL/SQL date calculation

1. Junior Member
Join Date
Nov 2006
Posts
3

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

2. 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)

3. Foreign Script Kiddie
Join Date
Aug 2002
Location
Posts
5,253
Originally Posted by gandolf989
Adding this causes date date to be 1 minute later
+(1/24*60)
+1/(24*60), surely?

4. 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```

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

6. Junior Member
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. Member
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. Member
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. Junior Member
Join Date
Nov 2006
Posts
3

## 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. 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;```

#### Posting Permissions

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