April 11, 2016 at 3:26 pm
Hi All,
I need to calculate age in years, months and days format between two dates (DateFrom and DateTo) in a way that:
If day of month in DateFrom is 1st then take it as a whole month
If day of month in DateFrom is not 1st then count days till the end of the month.
Example:
DateFrom='2010-02-01', DateTo='2011-03-11', Age= 1 Years, 1 Months 11 Days
DateFrom='2010-02-02', DateTo='2011-03-11', Age= 1 Years, 1 Months 8 Days
After calculating the age I'd need to sum the ages assuming that month is 30 days - in the above example I'd expect result: 2 Years, 2 Months, 19 Days.
I've tried the below function but it fails when values are e.g. DataFrom='2001-07-31', DateTo='2004-07-30'.
CREATE FUNCTION [dbo].[GetAge] (@DataFrom date, @DataTo date)
RETURNS @Tabela TABLE (Years INT, Months INT, Days INT)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @y int, @m int, @d int
SELECT @y= YEAR(@DataTo)- YEAR(@DataFrom),@m= MONTH(@DataTo)- MONTH(@DataFrom),@d=CASE WHEN DAY(@DataFrom)=1 THEN DAY(@DataTo)- DAY(@DataFrom)+1 ELSE DAY(@DataTo)- DAY(@DataFrom) -1 END
IF (@d<0)
BEGIN
SET @m=@m-1
SET @d=@d + DATEDIFF(d, @DataTo, EOMONTH(@DataTo))
END
IF(@m<0)
BEGIN
SET @y=@y-1
SET @m=@m+ 12
END
INSERT INTO @Tabela (Years, Months, Days)
SELECT @y,@m,@d
RETURN
END
April 11, 2016 at 10:37 pm
Calendar is not a thing for straight forward calculations.
You need a smarter approach.
1. Figure out the number of whole years between FromDate and ToDate
2. Add those whole years to FromDate and get a new FromDate.
3. Figure out the number of whole month between the new FromDate and ToDate.
4. Add the number of whole month to the new FromDate and get the "newer" FromDate.
5. Figure out the number of whole days between the "newer" FromDate and ToDate.
6. Return the 3 numbers you've figured out in requested format.
_____________
Code for TallyGenerator
April 11, 2016 at 11:08 pm
BTW,
Can you please explain the logic of this?
DateFrom='2010-02-02', DateTo='2011-03-11', Age= 1 Years, 1 Months 8 Days
To me, these dates
DateFrom='2010-02-02', DateTo='2011-03-02'
have 1 year, 1 month and 0 days between them.
If that's correct, than by adding 1 day to DateTo we increase the age by 1 day:
DateFrom='2010-02-02', DateTo='2011-03-03' - Age= 1 Years, 1 Months 1 Day
Going on:
DateFrom='2010-02-02', DateTo='2011-03-04' - Age= 1 Years, 1 Months 2 Days
DateFrom='2010-02-02', DateTo='2011-03-05' - Age= 1 Years, 1 Months 3 Days
DateFrom='2010-02-02', DateTo='2011-03-06' - Age= 1 Years, 1 Months 4 Days
DateFrom='2010-02-02', DateTo='2011-03-07' - Age= 1 Years, 1 Months 5 Days
DateFrom='2010-02-02', DateTo='2011-03-08' - Age= 1 Years, 1 Months 6 Days
DateFrom='2010-02-02', DateTo='2011-03-09' - Age= 1 Years, 1 Months 7 Days
DateFrom='2010-02-02', DateTo='2011-03-10' - Age= 1 Years, 1 Months 8 Days
DateFrom='2010-02-02', DateTo='2011-03-11' - Age= 1 Years, 1 Months 9 Days
Can you show where am I wrong?
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply