CODEWITHSUNDEEP
sql-servert-sql
Nahuatl
Nahuatl

Reputation: 132

SQL Server - Where Like statement with variable returning no results

I have a table with dates formatted as strings in the following manner YYYY-MM-DD. The data contains several years worth of data and I would like to pull just the current year using the following query:

Declare @currentYear VARCHAR(5)
Select @currentYear = year(getdate())

select *
from tablename
where datefixed like @currentYear

The query above is not returning any results. I've tried following the methodology outlined in this thread: Using variable in SQL LIKE statement

I'm missing something, but I can't put my finger on it.

Upvotes: 2

Views: 318

Answers (2)

MelgoV
MelgoV

Reputation: 656

You can use something like this:

Declare @currentYear int
Select @currentYear = year(getdate())

select *
from tablename
where year(convert(datetime,replace(datefixed,'-',''))) = @currentYear

If you want to use like operator, you must to ensure datefixed is a string type (varchar, char etc..) and add a wildcard (%)

Upvotes: 2

SqlZim
SqlZim

Reputation: 38073

You forgot the trailing wildcard %:

select *
from tablename
where datefixed like @currentYear + '%'

Upvotes: 3

Related Questions