SQL Server Performance

Splitting times between 2 given dates

Discussion in 'SQL Server 2008 General Developer Questions' started by Srikkanth, Mar 22, 2012.

  1. Srikkanth New Member

    I have 2 columsn StartDate and EndDate
    I want to write a select statement which should split the date on a hourly basis meaning
    if the start Date is "2012-03-21 08:00:00.00" and End Date is "2012-03-21 12:30:00.00"
    the outcome should be -
    Hour Duration(mins)
    8 60
    9 60
    10 60
    11 60
    12 30
    if the start Date is "2012-03-21 20:00:00.00" and End Date is "2012-03-21 20:15:00.00"
    the outcome should be
    Hour Duration(mins)
    20 15
    Srikkanth, Mar 22, 2012
    #1

  2. Madhivanan Moderator

    Start with this
    Code:
        declare @t table(startdate datetime, end_date datetime)
    insert into @t
    select '2012-03-21 08:00:00.000','2012-03-21 12:30:00.000'

    select
        datepart(hour,startdate)+number as hour,
        case when number<>datediff(hour,startdate,end_date) then 60 else datepart(minute,end_date) end as duration
    from @t as t1 cross join
        (select number from master..spt_values where type='p') as t2
    where
        t2.number between 0 and datediff(hour,startdate,end_date)
    Madhivanan, Apr 18, 2012
    #2

Share This Page