Forum Discussion

SmithToronto's avatar
SmithToronto
Copper Contributor
Sep 09, 2024

Age calculation with specified date

Hi, I want to get the following out from SQL query if date of birth is ‘1970-09-18’ and I want to calculate on ‘2024-09-16’. 2024-09-16 Minus 1970-09-18 to get result.   Desired output from que...
  • rodgerkong's avatar
    rodgerkong
    Sep 11, 2024

    SmithToronto 

    I think I resolved the problem, code is here

     

     

    CREATE FUNCTION CalcAge (@From AS Date, @To AS Date)
RETURNS VARCHAR(20)
BEGIN
	DECLARE @y int
	DECLARE @m int
	DECLARE @d int

	SET @y = DATEPART(year, @To) - DATEPART(year, @From)
	SET @m = DATEPART(month, @To) - DATEPART(month, @From)
	SET @d = DATEPART(day, @To) - DATEPART(day, @From)
	SET @d += 1

	IF @d < 0
	BEGIN
		SET @m -= 1
		IF DAY(@From) > DAY(EOMONTH(@To, -1))
			SET @d += DAY(EOMONTH(@From))
		ELSE
			SET @d += DAY(EOMONTH(@To, -1))
	END

	IF @m< 0
	BEGIN
		SET @y -= 1
		SET @m += 12
	END

	RETURN FORMAT(@y, '#00') + ':' + FORMAT(@m, '00') + ':' + FORMAT(@d, '00')
END
GO

     

     

    I made it as a function, you can test it with script like below

     

     

    DECLARE @i INT
DECLARE @b DATE
DECLARE @e DATE
SET @i= 0
SET @b= '2023-03-31'
SET @e= '2023-03-31'
WHILE (@i<368)
BEGIN
	PRINT CAST( @b AS varchar(20)) + ' to ' + CAST( @e AS varchar(20)) + ': '+ dbo.CalcAge(@b, @e)
	IF(DAY(@e) = DAY(EOMONTH(@e))) PRINT '-----------------------------------------'
	SET @e = DATEADD(day, 1, @e)
	SET @i += 1
END

     

    For your sample, this function will return result '53:11:30', different to your wish. That's think this way, we usually treat same day as a whole month(to your method, it is the day before birthday). For example, when birthday is 1970-09-18, we say 2024-09-17 is 54 years old, so 2024-09-16 is 1 day left to 54 years old, the last whole month to 16th/9 is Aug., and Aug. has 31 days, so 54 year decrease 1 day should by 53:11:30.

     

rodgerkong's avatar
rodgerkong
Iron Contributor

SmithToronto 

But there is a bug(maybe) you should know,  let's assume a boy was born on 2023-02-28，how old was he on 2023-03-27? Base on your sample it must be 00:01:00, then 2023-03-28? Will be 00:01:01. let's do this continue and make a list below:

DATE         AGE
2023-03-27   00:01:00
2023-03-28   00:01:01
2023-03-29   00:01:02
2023-03-30   00:01:03
2023-03-31   00:01:04
2023-04-01   00:01:02 ?
2023-04-02   00:01:03 ?
2023-04-03   00:01:04 ?
2023-04-04   00:01:05

 We can see, from 1st Apr. things go strange, boy's age roll back and same as 29th Mar. But what should be correct age? An other way is treat all days number that more than the last day of birth month as last day. For example, 2023-03-29, 2023-03-30, 2023-03-31 are treate as 2023-03-28, and the results list will like these:

DATE         AGE
2023-03-27   00:01:00
2023-03-28   00:01:01
2023-03-29   00:01:01
2023-03-30   00:01:01
2023-03-31   00:01:01
2023-04-01   00:01:02
2023-04-02   00:01:03
2023-04-03   00:01:04
2023-04-04   00:01:05

I know, It's still strange, but without roll back at least, I say it's a better strange:D

Which one do you like? Or do you have any sugguestion? It confused me from I saw your question.

 

rodgerkong's avatar
rodgerkong
Iron Contributor
Sep 11, 2024

SmithToronto 

I think I resolved the problem, code is here

 

 

CREATE FUNCTION CalcAge (@From AS Date, @To AS Date)
RETURNS VARCHAR(20)
BEGIN
	DECLARE @y int
	DECLARE @m int
	DECLARE @d int

	SET @y = DATEPART(year, @To) - DATEPART(year, @From)
	SET @m = DATEPART(month, @To) - DATEPART(month, @From)
	SET @d = DATEPART(day, @To) - DATEPART(day, @From)
	SET @d += 1

	IF @d < 0
	BEGIN
		SET @m -= 1
		IF DAY(@From) > DAY(EOMONTH(@To, -1))
			SET @d += DAY(EOMONTH(@From))
		ELSE
			SET @d += DAY(EOMONTH(@To, -1))
	END

	IF @m< 0
	BEGIN
		SET @y -= 1
		SET @m += 12
	END

	RETURN FORMAT(@y, '#00') + ':' + FORMAT(@m, '00') + ':' + FORMAT(@d, '00')
END
GO

 

 

I made it as a function, you can test it with script like below

 

 

DECLARE @i INT
DECLARE @b DATE
DECLARE @e DATE
SET @i= 0
SET @b= '2023-03-31'
SET @e= '2023-03-31'
WHILE (@i<368)
BEGIN
	PRINT CAST( @b AS varchar(20)) + ' to ' + CAST( @e AS varchar(20)) + ': '+ dbo.CalcAge(@b, @e)
	IF(DAY(@e) = DAY(EOMONTH(@e))) PRINT '-----------------------------------------'
	SET @e = DATEADD(day, 1, @e)
	SET @i += 1
END

 

For your sample, this function will return result '53:11:30', different to your wish. That's think this way, we usually treat same day as a whole month(to your method, it is the day before birthday). For example, when birthday is 1970-09-18, we say 2024-09-17 is 54 years old, so 2024-09-16 is 1 day left to 54 years old, the last whole month to 16th/9 is Aug., and Aug. has 31 days, so 54 year decrease 1 day should by 53:11:30.

 

Resources