SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calling functions in SQL (DateTime, Age, DateDif)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shawnmolloy
Yak Posting Veteran

USA
93 Posts

Posted - 04/11/2007 :  18:46:58  Show Profile  Visit shawnmolloy's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 04/11/2007 :  18:49:10  Show Profile  Visit dinakar's Homepage  Reply with Quote
...
...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)

USA
7020 Posts

Posted - 04/11/2007 :  19:32:14  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 04/11/2007 23:51:02
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000