sql - Need to replace date functions in where clause -

we have report functionality uses scalar functions pull report. tune extent converting functions stored procedure , applying best practices. found use date related functions in clause not sargable due following kind of conditons

and dbo.mefn_getdatedifference(c.date_of_birth,getdate(),'year')  >=66 

the function dbo.mefn_getdatedifference classic example of calculating age first compare years , month give result set.

as using c.date_of_birth inside function, index on date_of_birth not getting used. have better idea optimize this?

suppose 1 of customers birthdate '1939-05-20 00:00:00.000' if

select datediff(year,'1939-05-20 00:00:00.000',getdate())  

it gives me 75 years

but function dbo.mefn_getdatedifference calculates 74 per following logic

declare     @adstartdate datetime,     @adenddate datetime,     @asdatedifferencetype varchar(20)  set  @adstartdate='1939-05-20 00:00:00.000' set  @adenddate=getdate()   declare @idatedifference int  set @idatedifference = 0   select @idatedifference =   case when month(@adenddate) > month(@adstartdate)  datediff(year, @adstartdate, @adenddate)                                  when month(@adstartdate) = month(@adenddate) , day(@adenddate) >= day(@adstartdate) datediff(year, @adstartdate, @adenddate)                                  else datediff(year, @adstartdate, @adenddate) - 1 end      print @idatedifference 

so due kind of discrepancy using function better performance not want use

can't first create variable currentdate minus 67 or 66, , work variable in clause?

declare @calculatedate datetime set @calculatedate = dateadd(year, -66, getdate())  select *  (....) date_of_birth <= @calculatedate  

edit: response adk value of @calculatedate mar 11 1948 8:58am in example can see why use custom function, in example isn't necessary think.

when calculate date itself, not have calculate 'birthday years' anymore, cause whe've got right date itself, can calculate if 66 or older.