Reputation: 55
I have a query with PIVOT table, like,
SELECT *
FROM (SELECT ID, ID,Date FROM Table1) AS P
PIVOT
(
COUNT (P.ID)
FOR P.Date in ([08/17/2006],[08/18/2006]) -- [MM/DD/YYYY] - Format
) as pvt
In above query, P.Date
is datetime2(7)
so, I am getting the data like,
P.Date = '2006-08-17 15:30:23.0000000'
But i am comparing with '2006-08-17'
. Here date part is same, even though I am not getting the results.
My aim is to display the count of the ID when the date matches.
I have tried with Convert()
, But it is not allowing at PIVOT
table in FOR
block.
Upvotes: 0
Views: 369
Reputation: 247880
You need to do the CAST()
in the inner select:
SELECT *
FROM
(
SELECT ID, ID, Cast(yourDateValue as Date) newdate -- perform the conversion here
FROM Table1
) AS P
PIVOT
(
COUNT (P.ID)
FOR newdate in ([08/17/2006], [08/18/2006]) -- [MM/DD/YYYY] - Format
) as pvt
Upvotes: 2