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.
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. :)
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
(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
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
