Skip to Content
0
Oct 20, 2011 at 01:34 PM

WHERE clause is not working properly. Problem with Date Format.

150 Views

Dear All,

You may have seen something resembling this post in the past days. It's about dynamic pivot and the thread was closed but unanswered, never mind.

But my question here is about a different aspect of the problem.

I finally managed to make dynamic pivot work. See the query below which works perfectly:

DECLARE @listCol VARCHAR(2000)

DECLARE @query VARCHAR(4000)

SELECT @listCol = 
STUFF
(
( SELECT DISTINCT   '],['    +    CAST(month(T0.RefDate) AS varchar) 

FROM  JDT1 T0
FOR XML PATH('')

), 1, 2, ' ') +   ']'

SET @query =

'SELECT * FROM 

(SELECT Account, month (T0.RefDate) Month , Debit 

FROM JDT1 T0

------stops working if we add the WHERE clause just below this line----------------



GROUP BY Account, RefDate, Debit 
) S

PIVOT 
(
Sum(Debit) 
FOR Month IN ('+@listCol+')
) AS pvt'

EXECUTE (@query)

But if I add the foll. WHERE clause just below the dotted lines, it stops working. It appears to be a problem on date formatting.

Can anybody help me convert the date properly?

WHERE T0.RefDate >= '[%1]' and T0.RefDate <= '[%2]'

Error Message is:

Incorrect Syntax near '20110101'

if I input 01.01.11 in Selection Criteria (1 Jan 2011)

Thanks

Leon Lai