May 2, 2009 at 2:55 pm
Hi.
I'm working on a stored procedure, where i use a PIVOT to list statistics by hour. My problem is that DATEPART is "rounding up" (00:00:00 to 00:59:59 = 0). DATEPART will only return hours 0 to 23, which isn't logical for a novice user. I would like to have 0 to 24 hours, is that possible in any way?
DateCreated = DateTime
SELECT
[Date],
ISNULL([0],0) As [0],
ISNULL([1],0) As [1],
ISNULL([2],0) As [2],
ISNULL([3],0) As [3],
ISNULL([4],0) As [4],
ISNULL([5],0) As [5],
ISNULL([6],0) As [6],
ISNULL([7],0) As [7],
ISNULL([8],0) As [8],
ISNULL([9],0) As [9],
ISNULL([10],0) As [10],
ISNULL([11],0) As [11],
ISNULL([12],0) As [12],
ISNULL([13],0) As [13],
ISNULL([14],0) As [14],
ISNULL([15],0) As [15],
ISNULL([16],0) As [16],
ISNULL([17],0) As [17],
ISNULL([18],0) As [18],
ISNULL([19],0) As [19],
ISNULL([20],0) As [20],
ISNULL([21],0) As [21],
ISNULL([22],0) As [22],
ISNULL([23],0) As [23]
FROM
(
SELECT
1 As HitCount,
DATEADD(DAY, 0, DATEDIFF(DAY, 0, cs.[DateCreated])) As [Date],
DATEPART(HOUR, cs.[DateCreated]) As HourCreated
FROM
p PIVOT
(
SUM(HitCount) FOR HourCreated 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])
)
AS pvt
Best regards,
Carsten Petersen, Denmark
May 2, 2009 at 3:30 pm
It's returning correctly. 0 being midnight, 23 being 11 PM.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply