I have a query as

declare @aa varchar(50)

set @aa = concat('''',year(getdate())-67,'''')

select * from dimcustomer where cast(datepart(year,BirthDate) as varchar(50)) = @aa

I have datatype for birthdate as varchar(50). But unable to get any results, getting zero records

What is the wrong with my query ??? help is appreciated !!!

>Solution :

First, do NOT include single quotes with variable name to include them in SQL. Quotes are only needed for literal values in the SQL, not variables:

declare @aa varchar(50)
set @aa = year(getdate())-67
select * from dimcustomer where cast(datepart(year,BirthDate) as varchar(50)) = @aa

Second, do NOT convert datetime parts to compare as strings. This is (by far) the slowest and most-error prone way to do this.

declare @aa int
set @aa = year(getdate())-67
select * from dimcustomer where datepart(year,BirthDate) = @aa

Third, structure the comparison so you don’t need to make ANY modifications to the stored data. This will allow you match up with indexes and avoid per-row conversions for an entire table when you may only need a few of the rows:

declare @aa Date
set @aa = DateFromParts(year(getdate())-67, 1, 1)
select * from dimcustomer where BirthDate >= @aa and BirthDate < DATEADD(year, 1, @aa)

Fourth, NEVER define date columns as varchar. You can’t use functions like datepart() if it’s a varchar, you can’t index them properly, they take up more storage and memory, and you stop the database from ensuring you have valid data in the column.

As it is, you’ll need to cast the value to a datetime. This breaks any chance of index use on the column, which cuts to the core of database performance, and forces you to do the conversion on every row in the table… even rows you don’t need. Worse, thanks to cultural/internationalization issues these conversion are not the simple or fast conversions you might expect, but rather as really slow. Finally, it leaves you open to a broken query that won’t run if you get any data into the table that won’t convert:

declare @aa int
set @aa = year(getdate())-67
select * from dimcustomer where datepart(year,cast(BirthDate as date)) = @aa