CODEWITHSUNDEEP
sqlsql-server-2012
Hardik Gondalia
Hardik Gondalia

Reputation: 3717

Getting Error when Comparing Date in SQL Server

I want to compare DateTime field with input

Declare @Where varchar(MAX)

set @Where = @Where + 'EndDate >= ''' + cast('01-01-1970' as datetime) + ''' and EndDate <= ''' + cast('01-01-2200' as datetime) + ''''
print @Where

When I execute this Query I am getting error

Conversion failed when converting date and/or time from character string.

Upvotes: 0

Views: 524

Answers (2)

Philip Kelley
Philip Kelley

Reputation: 40289

The main issue is, you are trying to concatenate string data with datetime data. Presumably, you want to produce the string

EndDate >= '01-01-1970' and EndDate <= '01-01-2200'

Within your current statement you have

+ cast('01-01-1970' as datetime)
+ cast('01-01-2200' as datetime)

This will take a date in string form and convert it to datetime datatype--which, internally, is an 8 byte binary value. You then try to concatenate that with a string, and SQL is unable to do that. To fix this, start with your string:

set @Where = 'EndDate >= ''' + cast('01-01-1970' as datetime) + ''' and EndDate <= ''' + cast('01-01-2200' as datetime) + ''''

remove the unnecessary cast statements:

set @Where = 'EndDate >= ''' + '01-01-1970'+ ''' and EndDate <= ''' + '01-01-2200' + ''''

which can further be simplified to:

set @Where = 'EndDate >= ''01-01-1970'' and EndDate <= ''01-01-2200'''

One other thing, you have 

Declare @Where varchar(MAX)

Set @Where = @Where + <etc>

@Where is initialized as NULL, and when you concatenate (add) your configured string to it, the result will also be NULL. I’m guessing you are adding a clause to an existing WHERE statement; as this is a “new” clause, I’d recommend adding the appropriate boolean operator here, e.g.

Set @Where = @Where + ' AND ' + <etc>

Upvotes: 1

Thomas Soyer
Thomas Soyer

Reputation: 36

This should be. You can't concatenate a string with a datetime .

Maybe try this. 

Declare @Where varchar(MAX)

set @Where = @Where + 'EndDate >= ''' + cast(cast('01-01-1970' as datetime) as varchar) + ''' and EndDate <= ''' + cast(cast('01-01-2200' as datetime) as varchar) + ''''
print @Where

PS. You add the @Where variable, which is unseted (what means the value of @Where is NULL), to the constant values. And the result of adding NULL with something else is NULL. maybe try this

Declare @Where varchar(MAX)

set @Where =  'EndDate >= ''' + cast(cast('01-01-1970' as datetime) as varchar) + ''' and EndDate <= ''' + cast(cast('01-01-2200' as datetime) as varchar) + ''''
print @Where

Upvotes: 1

Related Questions