Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

need to look for ages 18-50

Status
Not open for further replies.

sonny1974

Technical User
Feb 25, 2007
161
US
I have a DOB field
i need to filter the dob field to look at 18-50 only
how can i put this in my where statement, so when a person runs this report in crystal they dont need to change the DOB to reflect 18-50 but will change the by it self daily so it reflects the right age

the field is DOB
 
hmm child-bearing years. Population analysis?

Do you need 18-50 years old to the day??




[monkey][snake] <.
 
yes that would be better then by the year
 
i work for the human servies for city of sf, i need to look at only people between 18-50.
 
is it possibe to do it by the day? that is what i would need
 
ok, this will work (it does it to the day):

Code:
where dateadd(dd, datediff(dd, DOB ,getdate()), 0) >= convert(datetime, '1918-01-01')
  and dateadd(dd, datediff(dd, DOB ,getdate()), 0) <= convert(datetime, '1950-01-01')

[monkey][snake] <.
 
will i need to adjust the days in the future or will it do automaticly?
 
I think this is what you are looking for. (and it's sargable too, so the preformance should be really good).

Code:
[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](id [COLOR=blue]Int[/color], DOB [COLOR=#FF00FF]DateTime[/color])
[COLOR=blue]SET[/color] [COLOR=#FF00FF]DATEFORMAT[/color] MDY
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, [COLOR=red]'1957-05-04'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, [COLOR=red]'1957-05-03'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, [COLOR=red]'1957-05-05'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, [COLOR=red]'1989-05-04'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, [COLOR=red]'1989-05-03'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, [COLOR=red]'1989-05-05'[/color])

[COLOR=blue]Select[/color] 	* 
[COLOR=blue]From[/color] 	@Temp
[COLOR=blue]Where[/color]	DOB >= [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Year[/color], -50, [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Day[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Day[/color], 0, [COLOR=#FF00FF]GetDate[/color]()), 0))
		And DOB < [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Year[/color], -18, [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Day[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Day[/color], 0, [COLOR=#FF00FF]GetDate[/color]()), 0)) + 1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
1957-05-05 00:00:00.000
should not be returned since that will be valid tomorrow not today (select dateadd(yy,50,'1957-05-05 00:00:00.000'))


vongrunt has a FAQ somewhere that deals with this stuff because datediffyy returns one even if the diff is one day

select datediff(yy,'2006-12-31 00:00:00.000','2007-01-01 00:00:00.000')




Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions





 
>> 1957-05-05 00:00:00.000 should not be returned

I think it should. Someone born on may 5, 1957 will turn 50 tomorrow. So, currently the age is 49 years, 364 (ish) days. That is clearly between 18 and 50.

Unless I'm missing something. [smile]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
You are right because you are doing this

Where DOB >='1957-05-04 00:00:00.000'
And DOB < '1989-05-05 00:00:00.000'

If you would do a datediff you would have potential problems


This is what I was talking about anyhow (not relevant in this case)

faq183-5842

10. How to calculate age (years elapsed)

Code:
DECLARE @dob smalldatetime; SET @dob = '19730105' 
DECLARE @today smalldatetime; SET @today = GETDATE()
SELECT DATEDIFF(yy, @dob, @today) - 
    CASE WHEN MONTH(@dob) > MONTH(@today) OR (MONTH(@dob) = MONTH(@today) AND DAY(@dob) > DAY(@today)) 
    THEN 1 ELSE 0 END

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions





 
will i need to adjust the days in the future or will it do automaticly?

sonny, in any of these cases, the where clause goes by today's date, whatever today is. There will be no need to adjust anything.

[monkey][snake] <.
 
with all these reply's (thanks for everyone trying to help) im kinda confused.
So how should i do this?
can i do it without a temp table?
 
I only included a table variable (in my example) to give you an example.

Essentially, just add the where clause that I show to your existing query.

Code:
Where DOB >= DateAdd(Year, -50, DateAdd(Day, DateDiff(Day, 0, GetDate()), 0))
      And DOB < DateAdd(Year, -18, DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)) + 1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
In my (not so) humble opinion, the query that I present is probably the best one to use. If there is an index on the DOB column, then my query will use that index and return the results faster. The other suggestions will cause a table scan which is bad for performance.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
thanks,

i really appriciate that everyone gave there expert opinion. this was very helpful
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top