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

Share This Page