# Function to calculate a new date colum data

Hi experts, I need create a function, similar to ROUND

Example, if I do:
``````select TO_CHAR (ROUND (TO_DATE ('11-SEP-1994 4:17 PM', 'DD-MON-YY HH:MI AM'), 'HH'),'DD-MON-YY HH:MI AM') from dual;
``````
I get:
``````11-SEP-94 04:00 PM
``````
I need to obtain:
``````11-SEP-94 04:15 PM
``````
The function should return the nearest point every 5 minutes.

Could you indicate me what I need for obtain this?

I need to apply this function in rows over a tables, and then convert a date column, with the new values calculated.

###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
>>nearest point every 5 minutes.

Rounded up or down?

So would '11-SEP-1994 4:18 PM' become "4:20 PM"?
0
Author Commented:
It would be down, but it is difficult to modify a solution to also obtain an up value?

Regards
0
Senior Oracle DBACommented:
This should work for rounding down.

``````SELECT Trunc(To_date('091119981617', 'mmddyyyyhh24mi'), 'hh') +
(
(
Floor(To_number(To_char(To_date('091119981617', 'mmddyyyyhh24mi'),
'mi'))
/
5)
*
5 ) /
1440 )
FROM   dual;
``````

If you would rather round up, change the Floor to Ceil.
0
Commented:
I know there is an easier way but I cannot find it in my bag of tricks.

I borrowed this one from:
http://www.freelists.org/post/oracle-l/Fun-with-SYSDATE-TRUNC-and-rounding

The problem with it is the 4:15.  It rounds it up to 4:20.

``````with mydata as (
select TO_DATE ('11-SEP-1994 4:14 PM', 'DD-MON-YY HH:MI AM') mydate from dual
union all
select TO_DATE ('11-SEP-1994 4:15 PM', 'DD-MON-YY HH:MI AM') mydate from dual
union all
select TO_DATE ('11-SEP-1994 4:17 PM', 'DD-MON-YY HH:MI AM') mydate from dual
)
select mydate,
trunc(mydate,'HH')+(trunc((mydate-trunc(mydate,'HH'))*24/(5/60))+1)/(60/5)/24
from mydata;
``````
0
Commented:
given date value D, try this...

TRUNC(d) + CEIL((TO_NUMBER(TO_CHAR(d, 'mi')) - 4) / 5) * 5 / 1440
0
Commented:
another version

TRUNC(d) + CEIL((((d - TRUNC(d)) * 1440) - 4) / 5) * 5 / 1440

test of both...

SELECT d,
TRUNC(d) + CEIL((TO_NUMBER(TO_CHAR(d, 'mi')) - 4) / 5) * 5 / 1440 ver1,
TRUNC(d) + CEIL((((d - TRUNC(d)) * 1440) - 4) / 5) * 5 / 1440 ver2
FROM (    SELECT TRUNC(SYSDATE) + LEVEL / 1440 d
FROM DUAL
CONNECT BY LEVEL < 2000)
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Slightly simpler would be to round as "normal" instead of forcing up or down

SELECT d,
TRUNC(d) + ROUND((d - TRUNC(d)) * 1440 / 5) * 5 / 1440
FROM (    SELECT TRUNC(SYSDATE) + LEVEL / 2880 d
FROM DUAL
CONNECT BY LEVEL < 3000)

Thus at 2:30 into each 5 minute window it will start rounding up, before that it will round down.
0
Author Commented:
Thank you all. Actually the three solutions work and do what I need.

Now I have left to me to include it in a function.

Regards
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.