Calculating Age form birth day in SQL

Transact-SQL
1

trying to use one line convert DOB to age but getting floats, any way to get ride of floats?

DATEDIFF(day, birthdate, GetDate())) / 365.25 as AGE,

image

2

cast result to an integer:

select cast(datediff(...) as int)

3

Not an accurate calc all the time, could be a year off.

4

Tried but the same?

CAST(DATEDIFF(day, birthdate, GetDate()) as int) / 365.25 as AGE,

5

Birthday field is VARCHAR(10)

6

This is going to cause a lot of problems - you really should store dates and either DATE or DATETIME data types. And this calculation is not accurate and will return invalid age depending on the current date and the birth date.

This is a more accurate calculation if you just want the person's age - and not a fractional age:

datediff(year, cast(birthdate As datetime), getdate()) - 
        Case When getdate() < dateadd(year, datediff(year, cast(birthdate As datetime), getdate()), getdate())
             Then 1
             Else 0
         End

Now - if you really want to use the above calculation (which will be incorrect at times):

CAST(DATEDIFF(day, CAST(birthdate AS datetime), getdate()) / 365.25 AS int) AS AGE,
7

OK Thanks Jeff. I 'll use the

datediff(year, cast(birthdate As datetime), getdate()) -
Case When getdate() < dateadd(year, datediff(year, cast(birthdate As datetime), getdate()), getdate())
Then 1
Else 0
End
for now.

8

Please put that in a function. Performance issues are negligible.

9

So you have said before. Its not my experience, nor that of people who I respect who have posted their results and finding in articles and blogs.

1 Like
10

You are wrong. They are wrong.

I proved to you that your test was in error.

11

Only for small rowsets. (or are you just joking?) This is a well-known issue in SQL Server.

12

OK -- be sure you test with millions of rows. post your reproducible timings using a function and using an inline expression.

13

You are writing code that is difficult to maintain. Please use functions.

IF there is a performance problem with a particular query, you will be hired to cut and paste the code.

14

functions are fine as long as they are not executed on every row. In that case, forget it. they are provably abysmal.

1 Like
15

Computer cycles are cheap. Yours are not.

The human brain can hold a finite # of items in short term memory. Your productivity will increase by reducing complexity into functions.

Make it readable and maintainable. The vast majority of code doesn't need perfect optimization. IF something is a performance problem, users will complain and you can fix it with a simple cut and paste. You'll look like a hero.

Use functions please.