asked on

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

kaufmed

What is the type of "BirthDay"?

Scott Craig

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.

Atouray

ASKER

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

Scott Craig

Just change every instance of RIGHT(BirthDate,4) to LEFT(BirthDate,4)

Atouray

ASKER

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

ASKER CERTIFIED SOLUTION

Scott Craig

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Atouray

ASKER

This is very fine.
i know why the year 2010 show 1.
that is from my DB.