How to Get Number of Months and Days From Two Dates

Question

I have two tables:

AgeMilestones //Represents available timespans
Id int     
Description varchar //(newborn, 1 month old, 2 month old, etc.)
NbrMonths int //( 0, 1, 2, etc.)
NbrDays int //(0, 1, 2, etc.)   


Child
Id int     
DateOfBirth DateTime  

I need to get the AgeMilestones that a given child has currently passed in age. The problem is that a real month can have 28 days, 30 days or 31 days. So if I convert NbrMonths into days, I may occasionally be off by a few days.

Is there any other way to do this that would be more accurate using the existing the table structure?

EDIT:
I need to figure what agemilesstone corresponds to the number of months/days that exist in the time between the child was born and today(something similar to below). I am getting tripped up in cases where an age milestone may be 3 months and 15 days, or 5 months and 7 days...

SET @Days = DateDiff(d,child.DateOfBirth, GetDate())  
SET @Months = DateDiff(m,child.DateOfBirth, GetDate()) 

SELECT * FROM AgeMileStone WHERE NbrMonths < @Months AND NbrDays < @Days 

Problem with records like
AgeMilestone:
Id: 4
Description: "5 and 1/2 months"
Months: 5
Days: 15

Answer 1

It's quite easy to do using datediff(month, DOB, getdate()).

Something like this:

declare @dob datetime = getdate() - 123; --born 123 days ago

select cast(datediff(month, @dob, getdate()) as varchar) + ' month old'
,cast(datediff(day, @dob, getdate()) as varchar) + ' days old'

Update

declare @dob datetime;
set @dob = getdate() - 125;

select 
datediff(month, @dob, getdate()) [Months], 
datediff(day, dateadd(month, datediff(month, @dob, getdate()), @dob), getdate()) [Offset Days]

Answer 2

I would suggest utilizing something like this:

DATEPART(Month, NOW())
DATEPART(DAY, NOW())

try using

SELECT datepart(dayofyear, c.DateOfBirth) as 'doy' from child c

Answer 3

Here is a query that uses the AgeMilestones table you have and the DATEADD function, which returns the list of milestones for a child born on a particular day.

-- setup the AgeMilestone table with some initial data
CREATE table AgeMilestone (milestone_month int, milestone_name varchar(50))
insert into AgeMilestone (milestone_month, milestone_name) values (1, '1 month')
insert into AgeMilestone (milestone_month, milestone_name) values (2, '2 month')
insert into AgeMilestone (milestone_month, milestone_name) values (3, '3 month')
insert into AgeMilestone (milestone_month, milestone_name) values (4, '4 month')
...
insert into AgeMilestone (milestone_month, milestone_name) values (12, '12 month')
insert into AgeMilestone (milestone_month, milestone_name) values (24, '24 month')

Declare @DOB DATETIME = '1/14/2009'
SELECT 
     milestone_month, milestone_name
FROM AgeMilestone
where DATEADD(month, milestone_month, @DOB) <= GETDATE()

Answer 4

I believe this solves it because the DATEADD function will take care of add the months and days appropriately given the starting date of birth:

declare @AgeMilestones table (
    NbrMonths int not null,
    NbrDays int not null,
    [Description] varchar(64) not null
)

declare @Child table (
    ChildId int not null identity,
    Name varchar(32) not null,
    DateOfBirth datetime not null
)

insert @AgeMilestones values (5, 15, '5 and 1/2 months')
insert @AgeMilestones values (0, 0, 'newborn')

insert @Child values ( 'Yearling', '2010-01-01' )
insert @Child values ( 'Newborn', GETDATE() )

declare @currentChild int = 2

select
    m.*
from @Child c
inner join @AgeMilestones m
    on dateadd(month, m.NbrMonths, dateadd(day, m.NbrDays, c.DateOfBirth)) <= getdate()
where c.ChildId = @currentChild