Computing age in months with decimal accuracy

• belldb

  Old Hand

  Points: 315

  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.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  October 2, 2009 at 8:03 am

  #1060951

  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

  Old Hand

  Points: 315

  More actions

  October 5, 2009 at 9:57 am

  #1061850

  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

  October 5, 2009 at 10:35 am

  #1061871

  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.