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 int
as
select * from
wm_user a
inner join
wm_user_details b
on
a.userid=b.userid
where
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 INT
AS
BEGIN
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 / 12
END
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 a
inner join
wm_user_details b
on
a.userid=b.userid
where
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 > 30
Thanks!
Calling functions in SQL (DateTime, Age, DateDif)
Posted - 04/11/2007 : 18:46:58
