need to look for ages 18-50

[sonny1974]

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

 

[klewis10367]

Use this:

where DATEDIFF(yy, dob, GETDATE())between 18 and 50

 

[sonny1974]

is the syntax ok? is it missing a )?

 

[sonny1974]

sorry i think the syntx is fine

 

[monksnake]

hmm child-bearing years. Population analysis?

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




<.

 

[sonny1974]

yes that would be better then by the year

 

[sonny1974]

i work for the human servies for city of sf, i need to look at only people between 18-50.

 

[sonny1974]

is it possibe to do it by the day? that is what i would need

 

[monksnake]

ok, this will work (it does it to the day):

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')

<.

 

[sonny1974]

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

 

[gmmastros]

I think this is what you are looking for. (and it's sargable too, so the preformance should be really good).

[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

 

[SQLDenis]

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

 

[gmmastros]

>> 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.

-George

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

 

[SQLDenis]

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)

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

 

[monksnake]

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.

<.

 

[sonny1974]

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?

 

[gmmastros]

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.

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

 

[gmmastros]

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

 

[sonny1974]

thanks,

i really appriciate that everyone gave there expert opinion. this was very helpful