How to query by decade

My Database has the following columns:
BirthDay, with the oldest person :109years old.
i need to create a chart that will show the birthrate for every 10years.
pls advice
this is my query but is not giving me what is wanted:

select COUNT(BirthDate)  AS BirthYear
From Bio_Data

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
What is the type of "BirthDay"?
Scott CraigWebmasterCommented:
If Birthdate is a full formatted date (e.g. 11/12/2011) then, you can use the following:

SELECT COUNT(BirthDate) AS BirthYear, (ROUND(CAST(RIGHT(BirthDate,4) AS INT)/10,0)*10) AS Decade FROM Bio_Data
	GROUP BY (ROUND(CAST(RIGHT(BirthDate,4) AS INT)/10,0)*10)

Open in new window

If it's not a fully formatted date, then you can just change the "RIGHT" formatting to be whatever you need to pull only the year from the date.
AtourayAuthor Commented:
Error message received:
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '1-01' to data type int.

NB: BirthDate is of the form:YYYY-MM-DD
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Scott CraigWebmasterCommented:
Just change every instance of RIGHT(BirthDate,4) to LEFT(BirthDate,4)
AtourayAuthor Commented:
Very closs to the answer:
i got the following result
BrithYear        decade
23674-----      1950
27051-----      1990
2737-----      1930
1----      2010
69725----      1970
38-------      1910
9739-----      1940
41794----      1960
106854---      1980
4      ---1900
493-------      1920

when i run this query to check for 2010
      select COUNT(BirthDate) from Bio_Data where Year(DateCreated)= 2010
i got 124170
Scott CraigWebmasterCommented:
The reason your check would be different is because DateCreated doesn't come into play in the original query at all.  Do you need to reference DateCreated?

The original query gives you a table of all the decades and how many people were born in those decades.  I'm not sure why you use the DateCreated to check it.  Maybe I don't fully understand your table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AtourayAuthor Commented:
This is very fine.
i know why the year 2010 show 1.
that is from my DB.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.