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;

Open in new window

I get:
11-SEP-94 04:00 PM

Open in new window

I need to obtain:
11-SEP-94 04:15 PM

Open in new window

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.

Thankyou in advanced!
carlino70Asked:
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.

slightwv (䄆 Netminder) Commented:
>>nearest point every 5 minutes.

Rounded up or down?

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

Thanks in advance.

Regards
0
johnsoneSenior 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; 

Open in new window


If you would rather round up, change the Floor to Ceil.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) 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;

Open in new window

0
sdstuberCommented:
given date value D, try this...

TRUNC(d) + CEIL((TO_NUMBER(TO_CHAR(d, 'mi')) - 4) / 5) * 5 / 1440
0
sdstuberCommented:
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

Your issues matter to us.

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

Start your 7-day free trial
sdstuberCommented:
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
carlino70Author 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.