dcsimg
Results 1 to 6 of 6

Thread: split to time periods

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,410

    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(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...
    Last edited by sapator; Feb 26th, 2018 at 10:10 AM.
    Slow as hell.

  2. #2
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,449

    Re: split to time periods

    Hi,

    well I use DateAdd

    sample VB6
    Code:
    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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,410

    Re: split to time periods

    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:
    Code:
    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))))
    Slow as hell.

  4. #4

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,410

    Re: split to time periods

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

    Code:
    	 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'
    Slow as hell.

  5. #5
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,449

    Re: split to time periods

    Quote Originally Posted by sapator View Post
    Simpler.I guess this will do, but will leave open.

    Code:
    	 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..
    Code:
    '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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  6. #6
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,315

    Re: split to time periods

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

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width