calculating age from ms sql table 1

[patrichek]

Hi,
I'm getting my [PatientDateOfBirth] from a table in MS SQL 2000 db. Its stored as 19600227 (yyyymmdd). i've tried several formats but Im' receiving "error" in my query. Any advice?

here's what i've been trying:

Int((DateDiff('d',[PatientDateOfBirth],Date())/365.25))

and

Age=DateDiff("yyyy", [PatientDateOfBirth], Now())+ _
            Int( Format(now(), "mmdd") < Format( [PatientDateOfBirth], "mmdd") )

neither have worked for me. the second code i was getting an error on the underscore and "Int" part...

thanks!

 

[PHV]

Use CDate(Left([PatientDateOfBirth],4) & '-' & Mid([PatientDateOfBirth],5,2) & '-' & Right([PatientDateOfBirth],2))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[patrichek]

HI PHV,
thanks for your reply. My original question was how to get the age from the date of birth? What you gave me formats it mm/dd/yyyy

thanks!

 

[patrichek]

i tried this one too (Posted by PHV) and it didn't work.

DateDiff("yyyy",[PatientDateOfBirth],Date())+(Format([PatientDateOfBirth],"mmdd")>Format(Date(),"mmdd"))

 

[PHV]

DateDiff("yyyy",CDate(Left([PatientDateOfBirth],4) & "-" & Mid([PatientDateOfBirth],5,2) & "-" & Right([PatientDateOfBirth],2)),Date())+(Format(CDate(Left([PatientDateOfBirth],4) & "-" & Mid([PatientDateOfBirth],5,2) & "-" & Right([PatientDateOfBirth],2)),"mmdd")>Format(Date(),"mmdd"))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[patrichek]

Hi PHV,
I made it work by using this: (i think!)

SELECT dbo_PatientMaster.PatientFirstName, dbo_PatientMaster.PatientLastName, Mid([ApptDate],5,2) & "/" & Right([ApptDate],2) & "/" & Left([ApptDate],4) AS [Appointment Date], dbo_ApptMaster.ApptTime, dbo_ApptMaster.ApptDoctor, dbo_ApptMaster.ApptComment, dbo_ApptMaster.ApptPhone, Mid([PatientDateofbirth],5,2) & "/" & Right([PatientDateofbirth],2) & "/" & Left([PatientDateofbirth],4) AS DOB, dbo_PatientMaster.PatientClassCode, Int(Year(Now())-Year([DOB])+(Month(Now())-Month([DOB]))/12+(Day(Now())-Day([DOB]))/365.25) AS Age
FROM dbo_ApptMaster INNER JOIN dbo_PatientMaster ON dbo_ApptMaster.ApptAccountNumber = dbo_PatientMaster.PatientAccountNumber
WHERE (((Mid([ApptDate],5,2) & "/" & Right([ApptDate],2) & "/" & Left([ApptDate],4))=[Forms]![VisionarySXSubFrm]![txtDate]) AND ((dbo_ApptMaster.ApptDoctor)=[Forms]![VisionarySXSubFrm]![DoctorCbo]))
ORDER BY dbo_ApptMaster.ApptTime;

 

[patrichek]

PHV,
Yours works too. thanks!

 

[MichaelRed]

strDate = "19600227"
? DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2))
2/27/1960

as a minor variation - just to show the conversion of the string to the date formatted numeric value.

Also please do a search for DOB and AGE in these (Tek-Tips) fora. There are a large number of threads purporting to accomplish this. Most do not work correctly in some range, usually when the "Age" is quite small.

These "errors" are mostly from the obscure methodology employed in the (Intrinsic) DateDiff function. It (DateDiff) only counts the boundary values between the two date objects. Thus:

? DateDiff("d", #12/31/05#, #1/1/06#)
 1 
? DateDiff("m", #12/31/05#, #1/1/06#)
 1 
? DateDiff("y", #12/31/05#, #1/1/06#)
 1

illustrating the curious math of "boundary" values in computing intervals.




not to pick on PHV, but I loosley translated his procedure into a VBA procedure:

Public Function basDOB2Age_PHV(strDate As String) As Long


    basDOB2Age_PHV = DateDiff("yyyy", CDate(Left(strDate, 4) _
                   & "-" & Mid(strDate, 5, 2) & "-" _
                   & Right(strDate, 2)), Date) _
                   + (Format(CDate(Left(strDate, 4) _
                   & "-" & Mid(strDate, 5, 2) & "-" _
                   & Right(strDate, 2)), "mmdd") > Format(Date, "mmdd"))



End Function

with the following results:

? basDOB2Age_PHV("19600227")
 46 
strDate = "19600227"

? basDOB2Age_PHV(CStr(strDate))
 46 

? basDOB2Age_PHV(Format(Date - 1, "yyyymmdd"))
 0 

? basDOB2Age_PHV(Format(Date + 1, "yyyymmdd"))
-1

, the LAST of which is particularly "interesting"

So, again, PLEASE do the search for the DOB and AGE things and study the plethora of approaches wto find THE one which actually works for ALL cases.

(p.s. then post that soloution here - again for the rest of us)






MichaelRed

 

[patrichek]

Hi MR,
I did do the search and tried a bunch of variations, i wasn't having much luck, that's why i asked here

thanks for the input

 

[raskew]

It may seem that the DateDiff() function should return Age based on Date of Birth (DOB) and a user-specified date. However, there's a problem with that solution since the DateDiff("yyyy"....) merely subtracts one year from another.

To accurately return age in years,we must know whether the month/day portion of the target date is >= than that of the DOB. We can add a boolean statement that examines month/days of the two dates and returns -1 (false) or 0 (true) to indicate if the month/day of the target date meets or exceeds that of the DOB. Here's an example:

x = 19600227
y = dateserial(left(x, 4), mid(x, 5,2), right(x, 2))
? y
2/27/60 
Age = DateDiff("yyyy", y, date()) +  (date() < DateSerial(Year(date()), Month(y), Day(y)))
? age
 46

HTH - Bob