Im my Crystal Command table i select the two following fields. d.birthdate and dd.draw_start_datetime. These are both Datetime fields.
I am trying to calculate within the select statement of my crystal command table the age at time of draw.
dd.draw_start_datetime - d.birthdate/365.25 as age. This works but i get this crazy long field with data down to micro seconds or some such thing. I just want the age in years. So if the person was 16 and 9 months at the time of visit i want the age to equal 16.
I have looked and read and read and looked and tried and i am not getting anywhere with this. Suggestions???
My current Command table select statement is as follows:
((sysdate-d.birthdate)/365) as age,
FROM Donor_Draw dd
LEFT JOIN Donor d on dd.donor_id=d.donor_id
LEFT JOIN Donor_Visit_Qual_Test dvqt on dd.visit_id=dvqt.visit_id
WHERE dd.draw_start_datetime>=sysdate-364 and
dd.actual_collection_type_cd IN ('SUR ALYX','ALYX','WB 480','WB ALLO','WB AUTO480','WB AUTOADJ','WB AUTOLOW','WB HH','WB THER') and
Note that i am using SYSDATE and birthdate in my select statement above to see if i can figure out the syntax.
Edited by cbaldwin - 14 Apr 2017 at 8:46am
have you tried DateDiff?
I would try: DateDiff("year",birthday, rundate)
if you get a negative number, reverse rundate and birthday.
another option might be to just divide by 365...the deicmal might imnply that you want a float (at least it does in SQL)