Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

I need to calculate age from DOB in one single line. I found the following by searching the web:

(datediff (month, DOBColumn, GETDATE())/12) as age

However, it only works on months and there is subsequently a small margin of error which I can't really live with since this software is for races with age groups. I don't want to create a function/SP.

(datediff (month, DOBColumn, GETDATE())/12) as age

However, it only works on months and there is subsequently a small margin of error which I can't really live with since this software is for races with age groups. I don't want to create a function/SP.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

as you noticed, datediff ( month of 31/12/2008 vs 01/01/2009 returns 1 and not 0...

Kelvin

declare @dt datetime

set @dt='02/04/1999'

select (datediff (YEAR, @dt, GETDATE())) as age --result is 10

GO

```
declare @dt datetime
set @dt='02/04/1999'
select (datediff (YEAR, @dt, GETDATE())) as age --result is 10
GO
declare @dt datetime
set @dt='03/04/1999'
select (datediff (YEAR, @dt, GETDATE())) as age --result is 10
GO
declare @dt datetime
set @dt='02/01/2009'
select (datediff (year, @dt, GETDATE())) as age --result is 0
GO
declare @dt datetime
set @dt='02/07/1980'
select (datediff (year, @dt, GETDATE())) as age --result is 29
GO
```

declare @dt datetime

set @dt='07/07/1980'

select (datediff (month, @dt, GETDATE())/12) as age --result is 28

GO

I'm currently trying:

SELECT DOB, CONVERT(int, DATEDIFF(day, DOB, GETDATE()) / 365.25) AS age

FROM tbEntrant

But it has variable results for today, some days on 26th are correct and others not.

declare @dt datetime

set @dt='07/07/1980'

select floor(CONVERT(decimal(5,2)

GO

```
declare @dt datetime
set @dt='09/07/1980'
select (datediff (year, @dt, GETDATE())) as age --result is 29
GO
declare @dt datetime
set @dt='09/07/1980'
select floor(CONVERT(decimal(5,2), datediff (month, @dt, GETDATE()))/12.00) as age --28
GO
```

```
declare @dt datetime
set @dt='07/07/1980'
select floor(CONVERT(decimal(5,2), datediff (month, convert(datetime,@dt,120), GETDATE()))/12.00) as age --28
GO
```

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial```
declare @dt datetime
set @dt='23 Apr 2009 13:26:57:153'
select floor(CONVERT(decimal(5,2), datediff (month, convert(datetime,@dt,120), GETDATE()))/12.00) as age --28
GO
declare @dt datetime
set @dt='2009/04/23'
select floor(CONVERT(decimal(5,2), datediff (month, convert(datetime,@dt,120), GETDATE()))/12.00) as age --28
GO
declare @dt datetime
set @dt='Apr 23 1999 1:26PM'
select floor(CONVERT(decimal(5,2), datediff (month, convert(datetime,@dt,120), GETDATE()))/12.00) as age --28
GO
declare @dt datetime
set @dt='23 Mar 1950'
select floor(CONVERT(decimal(5,2), datediff (month, convert(datetime,@dt,120), GETDATE()))/12.00) as age --28
GO
```

declare @dt datetime

set @dt='23 May 1950'

select floor(CONVERT(decimal(5,2)

GO

declare @dt datetime

set @dt='23 Jun 1950'

select floor(CONVERT(decimal(5,2)

GO

declare @dt datetime

set @dt='29 May 1950'

select floor(CONVERT(decimal(5,2)

GO

declare @dt datetime

set @dt='1 Jun 1950'

select floor(CONVERT(decimal(5,2)

GO

declare @dt datetime

set @dt='19500527'

select year(getdate() - @dt -1) - 1900 as age

I believe the reas0n you have to subtract -1 days is because '1900-01-01' = 0 so simply subtracting the base year of 1900 still leaves a one day rounding error.

The trouble with both this and Ritesh's solution is that they bomb out if the data is not a typical year (ie. year is 9000), which mine doesn't. I can't change my customer's data so I need a failproof solution and I'm willing to tolerate the minor errors in my current solution (some of the current days don't work out the year correctly - maybe 365.25 is the wrong year to use?).

The rules for leap years are:

In general terms the algorithm for calculating a leap year is as follows...

A year will be a leap year if it is divisible by 4 but not by 100. If a year is divisible by 4 and by 100, it is not a leap year unless it is also divisible by 400.

Thus years such as 1996, 1992, 1988 and so on are leap years because they are divisible by 4 but not by 100. For century years, the 400 rule is important. Thus, century years 1900, 1800 and 1700 while all still divisible by 4 are also exactly divisible by 100. As they are not further divisible by 400, they are not leap years.

Having supplied that, I'm not even goiung to attempt to dream up a solution to handle this.

Kelvin

Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

how about (datediff (day, DOBColumn, GETDATE())) as age

Gary