Search code examples
sqlsql-serverdatetimevarchar

converting datetime in varchar and using it in where clause in Sql server 2005

This is a sql query that i am working on. It is dumping the filtered result in a temp table it is giving an error stating "Incorrect syntax near ','."

Query.

DECLARE @year VARCHAR(50)
SET @year = '2012'
SELECT 
              [DAGName]
              , CONVERT(DATETIME, convert(VARCHAR(10), [ReportDate], 103), 103) AS FilteredDate        
                      INTO TempData FROM MailboxDatabase
        WHERE (CONVERT(VARCHAR(10), [ReportDate], 103)
        IN ( '01/01/'+ @year +,'01/02/'+ @year +,
             '01/03/'+ @year +,'01/04/'+ @year +,
             '01/05/'+ @year +,'01/06/'+ @year +,
             '01/07/'+ @year +,'01/08/'+ @year +,
             '01/09/'+ @year +,'01/10/'+ @year +,
             '01/11/'+ @year +,'01/12/'+ @year +
            ))

The error is coming in where condition in which I am trying to filter result on the basis of date by converting it into varchar. but it is not working .

Solution

  • You are concatenating the string in a wrong way.

    try this

    '01/01/'+ @year ,'01/02/'+ @year ,