Hello, I'm receiving a conversion error in my having clause. "Conversion failed when converting the varchar value '[2010-08-01]' to data type int" I have columns named [2010-08-01] respectively for each month... I'm trying to create a having clause that will only return rows greater than zero in the most current month. This works... having [2010-08-01] > 0 but when I write it like this (below), to make sure I always get the current month, I get a conversion error: having '['+ replace(convert(char(8),getdate(),111),'/','-') + '01]' > 0 I think...(from reseaching the problem) that SQL is converting the char to int when it sees '+'. I've tried converting the entire string but that didn't work either. Any ideas how I can write this where I won't get the error? Thanks in advance for any help!! Brian
You need to use dynamic SQL for this. When you say `[2010-08-01] > 0` you are saying 'compare the value in the column named 2010-08-01 to 0'. When you say `'['+ replace(convert(char(8),getdate(),111),'/','-') + '01]' > 0` you are saying 'compare the string value '[2010-08-01]' to 0'. To get this to work as you want, you need to use dynamic SQL whereby you build up the string and then execute it using `sp_executesql` or `EXEC`. Alternatively, you can use `UNPIVOT` to get the tabular results into a more natural table result, and query it using normal SQL.