Computing age in months with decimal accuracy

Question

Post reply

Computing age in months with decimal accuracy

belldb

SSC Veteran

Points: 255
More actions
October 2, 2009 at 7:12 am

#139225

I am working on a project to collect infant/child information and then provide their percentile range. The problem is that the data from the growth charts represent age in half month increments (0.5, 1.0, 1.5, 2.0, 2.5, etc.). I need a straightforward way to determine the age in months as a decimal value to determine which range they fall within.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 1

Maybe this?



declare @dob datetime

declare @today datetime

set @dob='20090508'

set @today=getdate()

select cast(datediff(day,@dob,@today)/15 as decimal(6,1))/cast(2 as decimal(6,1))

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

belldb SSC Veteran Points: 255 More actions · Answer 2

I want the result to be specific to a decimal, not averaged to the whole or half number value. See my working code.

Declare @dtDOB smalldatetime



Declare @dtDate smalldatetime

SET @dtDOB = '10/1/2009'

SET @dtDate = Getdate()

IF (datepart(mm, @dtDOB) = datepart(mm, @dtDate)) and (datepart(yy, @dtDOB) = datepart(yy, @dtDate))

BEGIN

Print datediff(dd, @dtDOB, @dtDate)/ 30.5

END

ELSE IF (datepart(dd, @dtDOB) < datepart(dd, @dtDate))

BEGIN

Print datediff(mm, @dtDOB, @dtDate) - (datepart(dd, @dtDate) - datepart(dd, @dtDOB)) / 30.5

END

ELSE IF (datepart(dd, @dtDOB) = datepart(dd, @dtDate))

BEGIN

Print datediff(mm, @dtDOB, @dtDate)

END

ELSE IF (datepart(dd, @dtDOB) > datepart(dd, @dtDate))

BEGIN

Print (datediff(mm, @dtDOB, @dtDate) - 1) + ((30.5 - datepart(dd, @dtDOB)) + datepart(dd, @dtDate)) / 30.5

END

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

There problem is that a month is not a set amount of time, so you have to define exactly what a half month and full month are?

If you are born on Jan 31, 2009, when are you a half month older? Feb 14, Feb 15, etc? On what date are you a full month older?

Once you define the rules for how you determine the age in decimal months, then developing code to do it should be fairly easy.