Age calculation with specified date

[SmithToronto]

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 query in format yy-mm-dd.

54-11-29

[#2]

@SmithToronto 

I think the output of your sample should be '53-11-29'

Code is here

DECLARE @b DATE
DECLARE @e DATE
DECLARE @y int
DECLARE @m int
DECLARE @d int

SET @e = '2024-09-16'  --2024-09-16
SET @b = '1970-09-18'  --1970-09-18

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

IF @d < 0 
BEGIN
	SET @m -= 1
	SET @d += DAY(EOMONTH(@b))
END

IF @d >= DAY(EOMONTH(@b))
BEGIN
	SET @m += 1
	SET @d -= DAY(EOMONTH(@b))
END

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

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

SELECT FORMAT(@y, '#00') + ':' + FORMAT(@m, '00') + ':' + FORMAT(@d, '00')

[#3]

Hi, Thanks so much

[#4]

@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.

[#5]

@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.