sdswiger
asked on
SQL / TSQL - DATETIME - To The Nearest Half Hour, Hour, 2nd Hour, MORE:
Hello:
I've searched quite a bit and found a few solutions but not anything solid that currently works. What I'm trying to do is basically round the time part to the nearest 30 minute, hour, etc...as displayed below:
Rounding the same as numbers such as if rounding to the nearest 10th, 4.9 is 0, 5 is 10, 12.2 is 10, etc...Same concept with time.
ITEMS LOOKING FOR: (the date should not be considered at all, will be removed, no problem on this)
1) Round to the nearest Half Hour
2) Round to the nearest Hour
* 3) Round to the nearest 2nd Hour in a 24-Hour period
** 4) Round to the nearest 4th Hour in a 24-Hour period
*** 5) Round to the nearest 6th Hour in a 24-Hour period
**** 6) Round to the nearest 12th Hour in a 24-Hour period
* 2ND HOUR - 24-Hour period would be 12:00 AM, 2:00 AM, 4:00 AM, etc...all the way to 10:00 PM being the last 2-Hour period.
** 4TH HOUR - 24-Hour period would be 12:00 AM, 4:00 AM, 8:00 AM, etc...all the way to 8:00 PM being the last 4-Hour period.
*** 6TH HOUR - 24-Hour period would be 12:00 AM, 6:00 AM, 12:00 PM, and 6:00, all being the complete 6-Hour period.
**** 12TH HOUR - 24-Hour period would be 12:00 AM and 12:00 PM, both being the complete 12-Hour period.
I've searched quite a bit and found a few solutions but not anything solid that currently works. What I'm trying to do is basically round the time part to the nearest 30 minute, hour, etc...as displayed below:
Rounding the same as numbers such as if rounding to the nearest 10th, 4.9 is 0, 5 is 10, 12.2 is 10, etc...Same concept with time.
ITEMS LOOKING FOR: (the date should not be considered at all, will be removed, no problem on this)
1) Round to the nearest Half Hour
2) Round to the nearest Hour
* 3) Round to the nearest 2nd Hour in a 24-Hour period
** 4) Round to the nearest 4th Hour in a 24-Hour period
*** 5) Round to the nearest 6th Hour in a 24-Hour period
**** 6) Round to the nearest 12th Hour in a 24-Hour period
* 2ND HOUR - 24-Hour period would be 12:00 AM, 2:00 AM, 4:00 AM, etc...all the way to 10:00 PM being the last 2-Hour period.
** 4TH HOUR - 24-Hour period would be 12:00 AM, 4:00 AM, 8:00 AM, etc...all the way to 8:00 PM being the last 4-Hour period.
*** 6TH HOUR - 24-Hour period would be 12:00 AM, 6:00 AM, 12:00 PM, and 6:00, all being the complete 6-Hour period.
**** 12TH HOUR - 24-Hour period would be 12:00 AM and 12:00 PM, both being the complete 12-Hour period.
ASKER
Hi momi,
Taking out the date part, vice versus, isn't a problem. It's rounding to the nearest time interval (i.e.: Half Hour, Hour, 2nd Hour, 4th Hour, 6th Hour, or 12th Hour) in a 24-Hour period, without date aspects considered, time only. I can actually get rounding to the nearest Hour fine, bit of trouble though when dealing with 30 minute, 2nd hour, etc...
Taking out the date part, vice versus, isn't a problem. It's rounding to the nearest time interval (i.e.: Half Hour, Hour, 2nd Hour, 4th Hour, 6th Hour, or 12th Hour) in a 24-Hour period, without date aspects considered, time only. I can actually get rounding to the nearest Hour fine, bit of trouble though when dealing with 30 minute, 2nd hour, etc...
ok
i did not really understand the logic by which you do the rounding, so i'll assume that if x<5 then x is rounded to 0, otherwise it is rounded to 10
assuming m holds the minutes, and h hold the hours
round to 30m :
if m < 15 then
begin
set @m = 0
end
else if m >= 15 and m < 45
begin
set @m = 30
end
else if m >= 45
begin
set @m = 0
set @h = @h + 1
end
something ilke that is ok ?
i did not really understand the logic by which you do the rounding, so i'll assume that if x<5 then x is rounded to 0, otherwise it is rounded to 10
assuming m holds the minutes, and h hold the hours
round to 30m :
if m < 15 then
begin
set @m = 0
end
else if m >= 15 and m < 45
begin
set @m = 30
end
else if m >= 45
begin
set @m = 0
set @h = @h + 1
end
something ilke that is ok ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you don't need to remove the date part from the time, you can use this instead.
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(@Time as float) * @Multiplier,0)/@Multiplier
return @RoundedTime
end
ASKER
Awesome, that was perfect! Thanks e!
datepart (arg, date_col) will return a part of a date according to arg
Datepart Abbreviations
to get the year use yy or yyyy
to get the month use mm or m
to get the day use dd or d
to get the hour use hh
to get the minute use mi or n
to get the second use ss or s
in order to assemble the date back, you just use the same function, and concat, for example
select cast( char(years)+'.'+char(month
the format depends on the format of dates that you use
this format is always acceptable
yyyymmdd
also consider using the dateadd function