Hi, I am writing a stored proc that will so a very simple search for users in the database. In my database I am storing a birthday as a datetime column called 'bday'. Users can search for people between a certain age range; say 23 - 30.Here is my search query:alter procedure sp_wm_quickSearch @lookingFor int,@mySex int,@country varchar(500),@ageTo int,@ageFrom int,@state varchar(10),@userid intasselect * from wm_user ainner join wm_user_details b on a.userid=b.useridwhere a.lookingfor = 0 and a.sex = 1 and a.country = 'United States of America' and a.state = 'Washington'
I found a simple function that looks like it can do what i need it to do:CREATE FUNCTION dbo.fnYearsApart( @FromDate DATETIME, @ToDate DATETIME)RETURNS INTASBEGIN RETURN CASE WHEN @FromDate > @ToDate THEN NULL WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1 ELSE DATEDIFF(month, @FromDate, @ToDate) END / 12END
MY QUESTIONS IS THIS:How do I call this function in my query and pass it the age, and use the age as a search param. I imagine it woudl look something like this:select * from wm_user ainner join wm_user_details b on a.userid=b.useridwhere a.lookingfor = 0 and a.sex = 1 and a.country = 'United States of America' and a.state = 'Washington' and fnYearsApart(a.bday, Some DateTime.Now function) < 24 and > 30
but this line doesn't work: (obviously i need to replace datetime.now fnYearsApart(a.bday, Some DateTime.Now function) < 24 and > 30Thanks!