• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

SQL Converting DateTime to a rounded off time

Hi.

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
0
NerishaB
Asked:
NerishaB
  • 2
  • 2
1 Solution
 
RimvisCommented:
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 
DATETIME) AS SMALLDATETIME)

Open in new window

0
 
Pratima PharandeCommented:
CREATE function [dbo].[RoundTime] (@Time datetime, @RoundTo float)
returns datetime
as
begin
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
end    


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
0
 
NerishaBAuthor Commented:
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?
0
 
RimvisCommented:
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 
DATETIME) AS SMALLDATETIME)

Open in new window

0
 
NerishaBAuthor Commented:
Thanks both your solutions helped.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now