Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mikecro
Starting Member
21 Posts |
 Posted - 2010-03-30 : 09:25:41
|
| I'm trying to do a rather simple query, but am getting the following error:Conversion failed when converting the varchar value '%' to data type int.It is a search query that is returning rows by year of start_dt. The year is passed as as parameter @year. The start_dt field is a datetime field. The reason I am using LIKE is because I pass % as the parameter value when I want it to include all dates.and year(b.start_dt) like '%' + @year + '%'I have also tried cast(year(b.start_dt) as varchar(4)) like '%' + @year + '%'and it returns the same error message. Any ideas?Thanks!! |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2010-03-30 : 09:32:25
|
perhaps year(b.start_dt) like case @year when '%' then b.start_dt else @year end http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
 |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-03-30 : 09:40:07
|
INT LIKE INTWhy do you want to use this sort of comparison? LIKE is meant to compare a string with another string (Or part of another string). Why use it to compare 2 ints?How about this? Make @year an INT, and pass a NULL value when you want all years. and year(b.start_dt) = INSULL(@year, year(b.start_dt)) There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
mikecro
Starting Member
21 Posts |
Posted - 2010-03-30 : 10:04:57
|
| Thanks for your help. I went with DBA in the Making's answer which works perfectly! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-30 : 13:37:00
|
if date field has an index on itand b.start_dt >= DATEADD(yy,@year-1900,0)and b.start_dt < DATEADD(yy,@year-1899,0) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-03-30 : 13:49:09
|
quote:
Originally posted by visakh16
if date field has an index on itand b.start_dt >= DATEADD(yy,@year-1900,0)and b.start_dt < DATEADD(yy,@year-1899,0)
That won't return all records if @year is NULL. In fact, it won't return any records.There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
|
|
|
|
|
Query by Year Variable