0

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

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
user5790
  • 61
  • 7
  • 2
    Why are you dividing dates in the first place? – Thom A Apr 28 '21 at 20:36
  • If you're expecting a decimal result you need to do /100000.0, this will convert the result to decimal as it has higher order of precedence. Why however is a mystery! – Stu Apr 28 '21 at 20:38
  • It's not a mystery at all, @Stu . An expression made up of `int`s is going to return a `int`. – Thom A Apr 28 '21 at 20:39
  • @Larnu lol Yeah I meant why subtracting and dividing the dates, not the division :) – Stu Apr 28 '21 at 20:40
  • 2
    You appear to be unaware of the `DATEDIFF` function, which can calculate differences without the need for these rather dubious casting practices. Even for advanced scenarios where you have to do more intricate calculations there's generally no need for using layers of conversions; see `DATEADD`, `DATETIMEFROMPARTS`. – Jeroen Mostert Apr 28 '21 at 20:43
  • What is the purpose of this query? – Schwern Apr 28 '21 at 20:44
  • 2
    @Larnu I want to calculate age using the method where you substract dates, e.g. now is 20210428, a person was born in 19990901, so his age is (20210428 - 19990901) = 21 – user5790 Apr 28 '21 at 20:46
  • @JeroenMostert Yeah, I am aware of it, it just won't work for my work – user5790 Apr 28 '21 at 20:47
  • That would be `DATEDIFF(MONTH, @date, GETDATE()) / 12`, or `12.0` and round up or down as required. `DATEDIFF` counts intervals crossed, not absolute difference, so a useful trick/guideline is to go down one level of granularity. – Jeroen Mostert Apr 28 '21 at 20:53
  • Does SQL Server not have an [interval type](https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT)? – Schwern Apr 28 '21 at 21:00
  • 1
    @Schwern thank yoy for your help, but I just want to stick to my method (weird but working) it is actually widely used – user5790 Apr 28 '21 at 21:08

1 Answers1

1

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.

Schwern
  • 153,029
  • 25
  • 195
  • 336