Skip to content
Advertisement

What is wrong with my implementation of this method to calculate a person’s age?

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;

Try it.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement