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

Question

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 .

Why specifying `+` . Remove that and you are good to go – praveen May 07 '13 at 09:03 — praveen, May 07 '13 at 09:03

score 1 · Accepted Answer · answered May 07 '13 at 09:04

1

You are concatenating the string in a wrong way.

try this

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

answered May 07 '13 at 09:04

Amit Rai Sharma

4,015
1
28
35

yes @ARS it was the dynamic query i have made previouly now i have removed extra +... it worked one more question........ – ankur May 07 '13 at 09:07
I want to get the name of the month from the temp table that i am making so I tried this SELECT DATENAME(month, FilteredDate) AS 'Month Name' it is giving synatax error.... any suggestion – ankur May 07 '13 at 09:08
It should work. I think there is some problem with the data. Check the data in filtereddate column – Amit Rai Sharma May 07 '13 at 09:14

Suraj Shrestha · Answer 2 · 2013-05-07T09:11:56.297

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
            ))

score -1 · Answer 3 · answered May 07 '13 at 08:58

-1

In Microsoft SQL Server:

--
-- Create test case
--
DECLARE @myDateTime DATETIME
SET @myDateTime = '2013-05-07'

--
-- Convert string
--
SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)

answered May 07 '13 at 08:58

NMALKO

168
1
7

it is not what i am looking for, I want the where condition to be filtered out without considering the time part.... – ankur May 07 '13 at 09:00
I'm sorry. It's my mistake. – NMALKO May 07 '13 at 09:01

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

3 Answers3