SQL Converting DateTime to a rounded off time

Posted on 2011-05-06
Last Modified: 2012-05-11

I have clock times that a person entered a building with.  I also have a field called Rounding, which sets a 0 fro false and 1 for true if we allow rounding or not.  There is another field called Rounding_Hours, which is a decimal field that requires a value for the amount of time we want to round by.  For instance, to round to the nearest 15 minutes, we set the value to be 0.25.

I want to round the clock times by the value set, but I need help doing so.  My code looks like this:

Calc_work_start_time = case when StartRounding_Up = 1   then  ** do something ** end
Question by:NerishaB
    LVL 19

    Accepted Solution

    Hi NerishaB,

    Try this:

    SELECT CAST(CAST(FLOOR(CAST(<YourTime> AS FLOAT)) + ROUND((CAST(<YourTime> AS FLOAT)-FLOOR(CAST(<YourTime> AS FLOAT)))*24.0/Rounding_Hours,0)*Rounding_Hours/24.0 AS 

    Open in new window

    LVL 39

    Expert Comment

    by:Pratima Pharande
    CREATE function [dbo].[RoundTime] (@Time datetime, @RoundTo float)
    returns datetime
    declare @RoundedTime smalldatetime
    declare @Multiplier float    
    set @Multiplier= 24.0/@RoundTo    
    set @RoundedTime= ROUND(cast(cast(convert(varchar,@Time,121) as datetime) as float) * @Multiplier,0)/@Multiplier
    return @RoundedTime

    select dbo.roundtime('13:15',0.5)

    This is an example in your query you can directly call this function
    Calc_work_start_time = case when StartRounding_Up = 1
    then dbo.roundtime(Calc_work_start_time,Rounding_Hours)
    else Calc_work_start_time

    Author Comment

    Thanks Rimvis,

    It does not seem to work correctly, For eg, the person clocked in at 07:35:23.  The rounded value should be 07:45:00, but it is rounding in the opposite direction, to give me 07:30:00.

    How can I fix this?
    LVL 19

    Expert Comment

    My code rounds data "to the nearest 15 minutes".  If you want to round up, try this:

    SELECT CAST(CAST(FLOOR(CAST(<YourTime> AS FLOAT)) + CEILING((CAST(<YourTime> AS FLOAT)-FLOOR(CAST(<YourTime> AS FLOAT)))*24.0/Rounding_Hours)*Rounding_Hours/24.0 AS 

    Open in new window


    Author Closing Comment

    Thanks both your solutions helped.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now