Date math, don't know if this is possible please help

I have a set of dates they are non contiguous. Like for instance
--StartDate--End_date--Title--
1/05/2003 2/15/2004 Superintendant
2/30/2005 5/25/2006 Manager
12/1/1987 1/01/2003 Superintendant
what I need to do is grab the fields that are superintendant only (which is
easy enough) but show would I look at those date ranges (without a function)
and add up the total years + days + months
something like this
xx years yy months and zz days?
--
Message posted via http://www.sqlmonster.comOn Aug 18, 6:26 pm, "scrocker via SQLMonster.com" <u36708@.uwe> wrote:
> I have a set of dates they are non contiguous. Like for instance
> --StartDate--End_date--Title--
> 1/05/2003 2/15/2004 Superintendant
> 2/30/2005 5/25/2006 Manager
> 12/1/1987 1/01/2003 Superintendant
> what I need to do is grab the fields that are superintendant only (which is
> easy enough) but show would I look at those date ranges (without a function)
> and add up the total years + days + months
> something like this
> xx years yy months and zz days?
> --
> Message posted viahttp://www.sqlmonster.com
It's a bit tricky to do this with 2 dates; however, using more than
that, you will most likely have to do the tabulation in the stored
procedure/query that is sourcing the report. For two dates, you might
be able to use something like:
=CStr(DateDiff("y", Fields!StartDate.Value, Fields!EndDate.Value)) + "
years " +
CStr(DateDiff("m", DateAdd("y", DateDiff("y", Fields!StartDate.Value,
Fields!EndDate.Value), Fields!StartDate.Value), Fields!EndDate.Value))
+ " months and " +
CStr(DateDiff("d", DateAdd("m", DateAdd("y", DateDiff("y", Fields!
StartDate.Value, Fields!EndDate.Value), Fields!StartDate.Value),
DateDiff("m", DateAdd("y", DateDiff("y", Fields!StartDate.Value,
Fields!EndDate.Value), Fields!StartDate.Value), Fields!EndDate.Value),
Fields!StartDate.Value)), Fields!StartDate.Value)) + " days"
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant