Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calling functions in SQL (DateTime, Age, DateDif)

Author  Topic 

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-04-11 : 18:46:58
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!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-11 : 18:49:10
...
...fnYearsApart(a.bday, getdate()) between 24 and 30

not sure if performance is a must here as we are using a function on a column.




************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-11 : 19:32:14
It would be better to calculate the start and end dates for the age range you desire, and select from the table like this:

declare @max_age int
declare @min_age int

declare @Today datetime
declare @Start_Date datetime
declare @End_Date datetime

-- Set age range
set @max_age = 39
set @min_age = 16

-- Set Today at midnight (00:00:00.000)
set @Today = dateadd(dd,datediff(dd,0,getdate()),0)

-- Set earliest date that is @max_age years old
set @Start_Date = dateadd(dd,1,dateadd(yy,-@max_age-1,@Today))

-- Set latest date that is @min_age years old
set @End_Date = dateadd(yy,-@min_age,@Today)


-- Selection query
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
-- Find birthdays between start and end dates
a.bday between @Start_Date and @End_Date


If you need to calculate age, you can use the Age Function, F_AGE_IN_YEARS, on this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462





CODO ERGO SUM
Go to Top of Page
   

- Advertisement -