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

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

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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.