split to time periods

[sapator]

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(cuurent), Datetime(+30).
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.

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...

[ChrisE]

Hi,

well I use DateAdd

sample VB6

Dim d As Date
 Dim d2 As Date
   
      d = DateAdd("n", 30, Now) 'add 30 min
      d2 = DateAdd("s", 30, Now) 'add 30 sec.
     
      
      MsgBox Format$(d, "hh:mm:ss") & vbCrLf & d2

regards
Chris

[sapator]

Hi.
I thought I posted in .net when i show your code.
No, I'm OK.
I'm not sure how would I go about and use the code in a T-SQL.

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))))

[sapator]

Simpler.I guess this will do, but will leave open.

	 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'

[ChrisE]

Simpler.I guess this will do, but will leave open.

	 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'

Hi,

that looks OK to me. I only have Access on the this PC

this is what I came up with..

'Table Adressen
'Field AD_Created is a DateTime

SELECT Adressen.AD_Created, DateAdd("n",30,Adressen.AD_Created) AS NewDateTime, Format$(DateAdd("n",30,[Adressen.AD_Created]),"hh:mm:ss") AS OnlyTime
FROM Adressen;

regards
Chris

[Ecniv]

Cant you just have a look up table of times?
When linking to it, filter the time part off the date and between the look up tables values.
(look up table as the times dont change - if the period is going to change, then a CTE, with a loop?)
Then join as above...

Perhaps you can call in a function and pass the time period to use..?