I want to calculate age using the method suggested here,
format the date to yyyymmdd and subtract the date of birth (also yyyymmdd) from the current date then drop the last 4 digits you’ve got the age
I tried this.
((CAST(CONVERT(nvarchar(30), GETDATE() , 112) AS INT))-CAST(CONVERT(nvarchar(30), @date, 112) AS INT)/100000)
But it returns me the same number 20210428
Advertisement
Answer
You’re very close, but there’s some precedence problems and you’re dividing by too much.
Let’s add some whitespace, the final frontier.
declare @date datetime = '1999-04-29'; select ( (CAST(CONVERT(nvarchar(30), GETDATE() , 112) AS INT)) - CAST(CONVERT(nvarchar(30), @date, 112) AS INT) / 100000 );
Because / has a higher precedence than -, it’s 20200428 – 19. Once that’s fixed we see that 1000000 is too much. 100,000 is 10^5 so it will remove 5 digits. If you want to remove 4 digits (2 for the month and 2 for the day) you want to integer divide by 10^4 or 10,000.
select ( CAST(CONVERT(nvarchar(30), GETDATE(), 112) AS INT) - CAST(CONVERT(nvarchar(30), @date, 112) AS INT) ) / 10000;