I am using SQL reporting services 2008 and report builder I have setup a report where I want to calculate the age of each student. 

I have table called Students with a DOB field. 

I want to display the age of each student and also use the age field in a parameter. 

Any one have any ideas on best way of doing this.

Do you want to show age in years (in this case just datediff(year, DOB, CURRENT_TIMESTAMP) or you want something more granular in years, months, days? If the latter, just search this forum, there is a thread with a solution by Georg

Because DATEDIFF counts boundaries you need to be a bit careful - if you use 'YEAR' as the first parameter any date last year will be counted as '1' and that is probably not what you want in this situation.

For example:

select DATEDIFF(YEAR, '20121231', CURRENT_TIMESTAMP) as Age
select DATEDIFF(DAY, '20121231', CURRENT_TIMESTAMP)/365 as Age

Age
-----------
1

(1 row(s) affected)

Age
-----------
0

(1 row(s) affected)

Hi,

Thank you for reply, I am sorry if these sound like really stupid questions as I am new to SQL reporting services.

Yes I want to work out the age in years.

I have the following report formula from crystal report which works fine. Effectively due to number of reasons we are moving all our reports over to SSRS. The age calculations date field below is from another table. I want to use the same logic to work out the date.

Crystal report formula for age in EY is as below.

Truncate (({EYT.AGE_CALCULATION_DATE}-{STUDENT.DOB})/365.25,0)

Thank you in advance for your help.

If you want to do this in an SSRS expression something like this should work:

=Floor(DateDiff("d", <DOB Here>, <Other Date Here>)/365.25)

• Proposed as answer by Naomi NModerator Thursday, November 14, 2013 8:43 PM

If you want to do this in an SSRS expression something like this should work:

=Floor(DateDiff("d", <DOB Here>, <Other Date Here>)/365.25)

• Proposed as answer by Naomi NModerator Thursday, November 14, 2013 8:43 PM

If you want to do this in an SSRS expression something like this should work:

=Floor(DateDiff("d", <DOB Here>, <Other Date Here>)/365.25)

• Proposed as answer by Naomi NModerator Thursday, November 14, 2013 8:43 PM

Hi,

Below is my query and at the end I am trying to work out the age of student but when I run this I get an error "ORA-00933: SQL command not properly ended". Any one got any ideas.

SELECT  s.forename, s.surname, to_char(s.DOB,'dd-Mon-yyyy') DOB, s.gender,s.ethnic_or, s.post_code,p.prov_name,e.term_desc,e.fin_yr,e.age_calculation_date,lc.paid_amt,       lc.paid_hrs_term,lc.paid_status,lc.paid_hrs_wk,lc.yearlyentitlement,lc.act_hrs_term,lc.act_hrs_wk,lc.act_wks_term,lc.oth_settings,lc.unpaid_hrs

  FROM     student s, provider p, ey_term e, la_service_provider lp, la_service_provider_detail ld, la_service_provider_service ls, la_serv_prov_child_hours lc, corresp_address c   WHERE   lc.term_id = e.term_id AND             lc.stud_id=s.stud_id AND             lc. LA_SERVICE_PROVIDER_DETAIL_ID =ld.LA_SERVICE_PROVIDER_DETAIL_ID AND              LD.LA_SERVICE_PROVIDER_SERVICE_ID = LS.LA_SERVICE_PROVIDER_SERVICE_ID AND      ls.LA_SERVICE_PROVIDER_ID = lp.LA_SERVICE_PROVIDER_ID AND      lp.EARLY_YEARS_PROVIDER_ID = P.PROVIDER_ID AND S.STUD_ID =C.entity_id (+)   select DATEDIFF(YEAR, '20121231', CURRENT_TIMESTAMP) as Age

• Edited by Wass5 Monday, November 18, 2013 7:10 PM

Hi,

Below is my query and at the end I am trying to work out the age of student but when I run this I get an error "ORA-00933: SQL command not properly ended". Any one got any ideas.

SELECT  s.forename, s.surname, to_char(s.DOB,'dd-Mon-yyyy') DOB, s.gender,s.ethnic_or, s.post_code,p.prov_name,e.term_desc,e.fin_yr,e.age_calculation_date,lc.paid_amt,       lc.paid_hrs_term,lc.paid_status,lc.paid_hrs_wk,lc.yearlyentitlement,lc.act_hrs_term,lc.act_hrs_wk,lc.act_wks_term,lc.oth_settings,lc.unpaid_hrs

  FROM     student s, provider p, ey_term e, la_service_provider lp, la_service_provider_detail ld, la_service_provider_service ls, la_serv_prov_child_hours lc, corresp_address c   WHERE   lc.term_id = e.term_id AND             lc.stud_id=s.stud_id AND             lc. LA_SERVICE_PROVIDER_DETAIL_ID =ld.LA_SERVICE_PROVIDER_DETAIL_ID AND              LD.LA_SERVICE_PROVIDER_SERVICE_ID = LS.LA_SERVICE_PROVIDER_SERVICE_ID AND      ls.LA_SERVICE_PROVIDER_ID = lp.LA_SERVICE_PROVIDER_ID AND      lp.EARLY_YEARS_PROVIDER_ID = P.PROVIDER_ID AND S.STUD_ID =C.entity_id (+)   select DATEDIFF(YEAR, '20121231', CURRENT_TIMESTAMP) as Age

• Edited by Wass5 Monday, November 18, 2013 7:10 PM

Hi,

Below is my query and at the end I am trying to work out the age of student but when I run this I get an error "ORA-00933: SQL command not properly ended". Any one got any ideas.

SELECT  s.forename, s.surname, to_char(s.DOB,'dd-Mon-yyyy') DOB, s.gender,s.ethnic_or, s.post_code,p.prov_name,e.term_desc,e.fin_yr,e.age_calculation_date,lc.paid_amt,       lc.paid_hrs_term,lc.paid_status,lc.paid_hrs_wk,lc.yearlyentitlement,lc.act_hrs_term,lc.act_hrs_wk,lc.act_wks_term,lc.oth_settings,lc.unpaid_hrs

  FROM     student s, provider p, ey_term e, la_service_provider lp, la_service_provider_detail ld, la_service_provider_service ls, la_serv_prov_child_hours lc, corresp_address c   WHERE   lc.term_id = e.term_id AND             lc.stud_id=s.stud_id AND             lc. LA_SERVICE_PROVIDER_DETAIL_ID =ld.LA_SERVICE_PROVIDER_DETAIL_ID AND              LD.LA_SERVICE_PROVIDER_SERVICE_ID = LS.LA_SERVICE_PROVIDER_SERVICE_ID AND      ls.LA_SERVICE_PROVIDER_ID = lp.LA_SERVICE_PROVIDER_ID AND      lp.EARLY_YEARS_PROVIDER_ID = P.PROVIDER_ID AND S.STUD_ID =C.entity_id (+)   select DATEDIFF(YEAR, '20121231', CURRENT_TIMESTAMP) as Age

• Edited by Wass5 Monday, November 18, 2013 7:10 PM

You're running against Oracle database. This is SQL Server forum. I suggest to ask your question in the Oracle forum.

Hi Naomi, I've used your query about and it is working for me. I just wanted to ask why you are using 365.25 instead of 365 only. Thank you.

The 365.25 will correct some dates where 365 will give an incorrect answer.

But I wouldn't use it, because it will still give incorrect answers in some situations.

For many different solutions that will always return correct result, check out How to determine a person's age using TSQL

365.25 takes into account leap year math when dealing with calculations finding a year by dividing by number of days. Effectively, every 4 years, it adds a day to the divisor. 

Age / service time calculation: http://www.sqlusa.com/bestpractices2005/employment/