Results 1 to 5 of 5

Thread: date Intervals

  1. #1
    Join Date
    Apr 2004
    Location
    Melbourne
    Posts
    144

    Unanswered: date Intervals

    Is that like some interval function for dates, like i want to group my data in intervals of 15 minutes and 30 minutes. Is there such a function in T-SQL

  2. #2
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    Not really. But you can use divide to achive this. See below for illustration
    Code:
    declare 
        @datetable table
        (
            date_col    datetime
        )
    insert     into @datetable(date_col) 
    select     dateadd(minute, n1 + n2, '2006-01-01 01:00')
    from    
    (
        select    0 as n1 union all select 1 union all select 2 union all
        select  3       union all select 4 union all select 5 union all
        select  6       union all select 7 union all select 8 union all
        select  9
    ) as num1
    cross join
    (
        select  0 as n2 union all
        select 10 union all select 20 union all select 30 union all
        select 40 union all select 50 union all select 60
    ) as num2
    
    select     date_col, datediff(minute, 0, date_col) / 15 as timeslot -- 15 mins interval
    from     @datetable
    order by timeslot, date_col
    -----------------
    KH


  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Slight variation: Use MOD operatore instead of divisor, and convert back to datetime value -
    Code:
    select	date_col,
    	dateadd(minute, datediff(minute, 0, date_col) % 15, 0)
    from	@datetable
    order by timeslot, date_col
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2002
    Posts
    229
    You can also experiment with datepart() to get parts of the date.

  5. #5
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    This will group the time
    01:00, 01:15, 01:30, 01:45 as one group,
    01:01, 01:16, 01:31, 01:46 as another group.

    Well, It really depends on what jcwc888 means by "intervals of 15 minutes"
    -----------------
    KH


Posting Permissions

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