Split to time periods

Hi.
MS Sql server.
I need to split a column to time periors (+30 minutes).
So I have only one column with datetime.
I need to do:
Value, Datetime(current) ---- Datetime(+30). One 30 minutes period
Value, Datetime(another datetime) ---- Datetime(+30). Another 30 minutes period
How would I go about?
I was thinking to have a min max or a datediff but or CTE but I'm not sure how to go about.
I've seen examples that consider an existing time period table but on my issue the main table has data + datetime.
Any suggestions?
Thanks.

Edit.So to be more precise:

Value 2018-01-01 15:00 -- 2018-01-01 15:30
Value 2018-01-01 15:00 -- 2018-01-01 15:30
Value 2018-01-01 15:00 -- 2018-01-01 15:30
Value 2018-01-01 15:31 -- 2018-01-01 16:00
Value 2018-01-01 15:31 -- 2018-01-01 16:00
Value 2018-01-01 16:01 -- 2018-01-01 16:30
Values 2018-01-01 16:01 -- 2018-01-01 16:30
----From one column
etc...

I have fished this from the net and I'm trying to manipulate it but it's complicated.Although I can use it, it still is complicated, so an easier version would be preferred.

So on 15 minutes, it goes like this:

select OrderH_intID, OrderH_dtmInitiated, -- this is the time I have
          dateadd(minute,
      (datepart(minute, OrderH_dtmInitiated) / 15) * 15,
      dateadd(hour, 
         datepart(hour, OrderH_dtmInitiated), 
         convert(datetime, convert(varchar, OrderH_dtmInitiated, 1))))
          --,sum(StaffingRequirement), sum(ActiveStaff)  ---etc
          from tblorderhistory
where OrderH_dtmInitiated > '20180226 14:19:00'
          group by OrderH_intID,OrderH_dtmInitiated, 
          dateadd(minute,
      (datepart(minute, OrderH_dtmInitiated) / 15) * 15,
      dateadd(hour, 
         datepart(hour, OrderH_dtmInitiated), 
         convert(datetime, convert(varchar, OrderH_dtmInitiated, 1))))
select 
  dt
  ,dateadd(minute,30,dateadd(minute,datepart(minute,dt)/30*30, dateadd(hour,datediff(hour,0,dt),0))) as new_value
from
 (values ('2018-01-01 15:00:00'),('2018-01-01 15:41:40') ) as a(dt)

output:

|dt                 |new_value|
|2018-01-01 15:00:00|01/01/2018 15:30:00|
|2018-01-01 15:41:40|01/01/2018 16:00:00|
  • create a value holding date and only hour dateadd(hour,datediff(hour,0,dt),0)
  • round minutes to the previous half , lower then current time datepart(minute,dt)/30*30
  • previous expression combine them into a date with dateadd : dateadd(minute,datepart(minute,dt)/30*30, dateadd(hour,datediff(hour,0,dt),0))
  • add 30 minutes to previous expression

detailed here :

select 
  dt
  ,dateadd(hour,datediff(hour,0,dt),0) as ValueWithOnlyHours
  ,datepart(minute,dt)/30*30 as RoundTime
  ,dateadd(minute,datepart(minute,dt)/30*30, dateadd(hour,datediff(hour,0,dt),0)) as RoundedDateTime
  ,dateadd(minute,30,dateadd(minute,datepart(minute,dt)/30*30, dateadd(hour,datediff(hour,0,dt),0))) as new_value_with_30Minutes
from
 (values ('2018-01-01 15:00:00'),('2018-01-01 15:41:40') ) as a(dt)

|dt|ValueWithOnlyHours|RoundTime|RoundedDateTime|new_value_with_30Minutes|
|2018-01-01 15:00:00|01/01/2018 15:00:00|0|01/01/2018 15:00:00|01/01/2018 15:30:00|
|2018-01-01 15:41:40|01/01/2018 15:00:00|30|01/01/2018 15:30:00|01/01/2018 16:00:00|

1 Like

Seems simpler.
Thanks!

	 select OrderH_intID,
  OrderH_dtmInitiated
  ,dateadd(minute,30,dateadd(minute,datepart(minute,OrderH_dtmInitiated)/30*30, dateadd(hour,datediff(hour,0,OrderH_dtmInitiated),0))) as new_value
from
tblorderhistory as OrderH_dtmInitiated
where OrderH_dtmInitiated > '20180226 14:19:00'

Important is to get the idea behind it.
Definitely are more approaches to this. This is only one of it.

Alternative: dateadd(minute,ceiling(datediff(minute,-0.00000002,orderh_dtminitiated)/30.0)*30,0)