CODEWITHSUNDEEP
sqlsql-serverdate
Richard
Richard

Reputation: 21

Split date column into hour segments

Thank you in advance for taking the time to look at this.

I am looking to take a number of records containing a date field and split them into hour columns with a count in each (sql server).

E.g.

SpecialDateColumn  
14/1/15 10:23      
14/1/15 11:34       
14/1/15 12:45       
14/1/15 12:55

I'm looking the results in a single row as follows:

Date      10  11  12  13 etc
14/1/15   1   1   2   0

I've tried to do this using a pivot table, but not had much joy.

Thanks again in advance.

Upvotes: 0

Views: 2493

Answers (4)

Kishore Kumar
Kishore Kumar

Reputation: 78

You can do this : 

SELECT *
FROM (
SELECT SpecialDateColumn AS [Date]
    ,DATEPART(HOUR, SpecialDateColumn) [Hour]
FROM < TABLE >
) AL1
PIVOT(COUNT([Hour]) FOR [Hour] IN (
        [0]
        ,[1]
        ,[2]
        ,[3]
        ,[4]
        ,[5]
        ,[6]
        ,[7]
        ,[8]
        ,[9]
        ,[10]
        ,[11]
        ,[12]
        ,[13]
        ,[14]
        ,[15]
        ,[16]
        ,[17]
        ,[18]
        ,[19]
        ,[20]
        ,[21]
        ,[22]
        ,[23]
        )) P;

Upvotes: 1

Stefan Michev
Stefan Michev

Reputation: 5093

Pivot is the right way imho ... in the snippet below I have an images Table with a field created_date

select
    *
from
(
    select
        1 as dummy                       ,
        datepart(hh, created_date)  as h ,
        cast(created_date as date)  as d
    from images
) as t
pivot( count(t.dummy) for t.h in ([9],[10],[11],[12]) ) as pvt

and the result from query looks lihe this:

enter image description here

Upvotes: 0

mxix
mxix

Reputation: 3659

This way will always get all the hours but is an example with PIVOT. Other than this you can use dynamic SQL to construct the PIVOT either with CASES like Gordon's example or PIVOT

select
    *
from (
    select
        CONVERT(DATE,h) D,
        DATEPART(HOUR,h) H
    from (
    select
    '2014-01-01 10:00:01' h
    UNION ALL
    select
    '2014-01-02 11:00:01'
    UNION ALL
    select
    '2014-01-03 10:00:01'
    UNION ALL
    select
    '2014-01-03 14:00:01'
    ) T
) SRC
PIVOT(
    COUNT(H) 
    FOR H IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])
) PVT

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269533

It is simple enough to write this as conditional aggregation:

select cast(SpecialDateColumn as date) as thedate,
       sum(case when datepart(hour, SpecialDateColumn) = 10 then 1 else 0 end) as hour_10,
       sum(case when datepart(hour, SpecialDateColumn) = 11 then 1 else 0 end) as hour_11,
       sum(case when datepart(hour, SpecialDateColumn) = 12 then 1 else 0 end) as hour_12,
       sum(case when datepart(hour, SpecialDateColumn) = 13 then 1 else 0 end) as hour_13
from table t
group by cast(SpecialDateColumn as date)
order by thedate;

Upvotes: 1

Related Questions