How to display roundof time

Question

Using SQL Server 2005

Table1

ID Intime Outtime

001 00.21.00 00.48.00
002 08.23.00 13.45.00
003 00.34.00 00.18.00

I need to display the time time like 30 minutes or 1 Hours, it should display a roundoff time

Expected Output

ID Intime Outtime

001 00.30.00 01.00.00
002 08.30.00 14.00.00
003 01.00.00 00.30.00

How to make a query for the roundoff time.

Answer 1

Take a look at the DATEDIFF, DATEADD and DATEPART. You should be able to do what you want with that.

http://msdn.microsoft.com/en-us/library/ms189794.aspx

http://msdn.microsoft.com/en-us/library/ms186819.aspx

http://msdn.microsoft.com/en-us/library/ms174420.aspx

Here is kind of a step-by-step routine. I'm sure you can do something shorter and even more efficient. It would also simplify a lot if you used a datetime data type instead of a string.

declare @T table (id char(3), intime char(8), outtime char(8))

insert into @T values ('001', '00.21.00', '00.48.00')
insert into @T values ('002', '08.23.00', '13.45.00')
insert into @T values ('003', '00.34.00', '00.18.00')

;with
cteTime(id, intime, outtime)
as
( -- Convert to datetime
  select
    id,
    cast(replace(intime, '.', ':') as datetime),
    cast(replace(outtime, '.', ':') as datetime)
  from @T
),
cteMinute(id, intime, outtime)
as
( -- Get the minute part
   select 
     id,
     datepart(mi, intime),
     datepart(mi, outtime)
   from cteTime  
),
cteMinuteDiff(id, intime, outtime)
as
( -- Calcualte the desired diff
  select
    id,
    case when intime > 30 then (60 - intime) else (30 - intime) end, 
    case when outtime > 30 then (60 - outtime) else (30 - outtime) end 
  from cteMinute
),
cteRoundTime(id, intime, outtime)
as
( -- Get the rounded time
  select
    cteTime.id,
    dateadd(mi, cteMinuteDiff.intime, cteTime.intime),
    dateadd(mi, cteMinuteDiff.outtime, cteTime.outtime)
  from cteMinuteDiff
    inner join cteTime
      on cteMinuteDiff.id = cteTime.id
),
cteRoundedTimeParts(id, inHour, inMinute, outHour, outMinute)
as
( -- Split the time into parts
  select 
    id,
    cast(datepart(hh, intime) as varchar(2)) as inHour,
    cast(datepart(mi, intime) as varchar(2)) as inMinute,
    cast(datepart(hh, outtime) as varchar(2)) as outHour,
    cast(datepart(mi, outtime) as varchar(2)) as outMinute
  from cteRoundTime 
),
cteRoundedTime(id, intime, outtime)
as
( -- Build the time string representation
  select 
    id,
    right('00'+inHour, 2)+'.'+right('00'+inMinute, 2)+'.00',
    right('00'+outHour, 2)+'.'+right('00'+outMinute, 2)+'.00'
  from cteRoundedTimeParts
)
select *
from cteRoundedTime

Answer 2

You can round the current date to 30 minutes like:

select dateadd(mi, datediff(mi,0,getdate())/30*30, 0)

Explanation: this takes the number of minutes since the 0-date:

datediff(mi,0,getdate())

Then it rounds that to a multiple of 30 by dividing and multiplying by 30:

datediff(mi,0,getdate())/30*30

The result is added back to the 0-date to find the last 30 minute block

dateadd(mi, datediff(mi,0,getdate())/30*30, 0)

This can be adjusted easily for 60 minutes. :)

Answer 3

By checking the range

select ID,
   DateAdd(mi, DateDiff(mi, 0, Intime +
      case when InMi >= 15 then 30 - InMi else - InMi end), 0) as Intime,
   DateAdd(mi, DateDiff(mi, 0, Outtime +
      case when OutMi >= 15 then 30 - OutMi else - OutMi end), 0) as Outtime
FROM
(
select ID, Intime, Outtime,
datepart(mi, InTime) % 30 InMi,
datepart(mi, Outtime) % 30 OutMi
from tbl
) X

or by using the classical trick equivalent to Int(x+0.5)..

select ID,
    dateadd(mi, ((datediff(mi, 0, Intime)+15)/30)*30, 0) Intime,
    dateadd(mi, ((datediff(mi, 0, Outtime)+15)/30)*30, 0) Outtime
from tbl

IF you want to ROUNDUP instead

(you have a value going from 00.34.00 to 01.00.00) Then you need this

select ID,
    dateadd(mi, ((datediff(mi, 0, Intime)+29)/30)*30, 0) Intime,
    dateadd(mi, ((datediff(mi, 0, Outtime)+29)/30)*30, 0) Outtime
from tbl