# Calculate age with decimals when age < 0 and no decimal when age >0

• ### Question

• Hi All,

I have DOB Date column, need to calculate age with Getdate()

DOB
07/16/1997
09/04/2013

Output should look like this..

07/16/1997 17 ( no decimals after age when age is > 0)
09/04/2013 0.98 ( need decimals after age when age is < 0)

I know we have to use DateDiff to get age but results are not accurate..

DATEDIFF(DAY,DOB,GETDATE())

Thanks,
RH

sql

Wednesday, August 27, 2014 3:09 PM

• I'm not sure how accurate you need to be but here's one option.

```with cteDOB
as
(
select CAST('8/26/1969' as date) [DOB]
union all
select CAST('9/24/1967' as date) [DOB]
union all
select CAST('6/27/1941' as date) [DOB]
union all
select CAST('6/5/2014' as date) [DOB]
)
select
dob
,DATEDIFF(year,dob,sysdatetime()) [yrs]
,DATEDIFF(month,dob,sysdatetime())%12 [mnth]
,DATEDIFF(year,dob,sysdatetime())+((DATEDIFF(month,dob,sysdatetime())%12)/12.0) [raw age]
,case sign(datediff(year,dob,sysdatetime()))
when 1
then DATEDIFF(year,dob,sysdatetime())

else
((DATEDIFF(month,dob,sysdatetime())%12)/12.0)
end [age in years]
from cteDOB;   ```

Thursday, August 28, 2014 10:40 AM

### All replies

• The output can only have a single data type, which would be decimal in this case. So output for age>0 would look like 07/16/1997 17.00. To find out whether age >0 or age <=0, use "CASE WHEN".

If you want different data types, then you can CAST the result to INT

Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

Wednesday, August 27, 2014 3:15 PM
• I tried case statement like this.. but still i see decimals

CASE WHEN CAST(DATEDIFF(DAY,DOB,GETDATE())/365.25 AS int) <= 0 THEN CAST(DATEDIFF(DAY,DOB,GETDATE())/365.25 AS DECIMAL(5,2))
ELSE CAST(DATEDIFF(DAY,DOB,GETDATE())/365.25 AS int) END AS Age

Thanks!

sql

Wednesday, August 27, 2014 3:18 PM
• As noted above, you can do string conversion and truncation once the result is calculated.

Age calculation example:

http://www.sqlusa.com/bestpractices2005/employment/

Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Wednesday, August 27, 2014 3:29 PM
• does a ".00" in the result bother you? As said before, the output of a query can only have ONE data type, that is it cannot return DECIMAL here and INT there

Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

Wednesday, August 27, 2014 4:01 PM
• I agree with Bodo and Kalman.

Can you try the below?

```declare @DOB date='09/04/2013'
-- declare @DOB date='07/16/1997'
select CAST(DATEDIFF(DAY,@DOB,GETDATE())/365.25 AS decimal(5,2))

SELECT
CASE WHEN CAST(DATEDIFF(DAY,@DOB,GETDATE())/365.25 AS decimal(5,2)) <1
THEN CAST(CAST(DATEDIFF(DAY,@DOB,GETDATE())/365.25 AS DECIMAL(5,2)) AS varchar(10))
ELSE
substring(CAST((CAST(DATEDIFF(DAY,@DOB,GETDATE())/365.25 as INT)) as varchar(10)),1,2) END AS Age

```
--Prashanth

Wednesday, August 27, 2014 4:03 PM
• Try this. Hope this help

```;WITH CTE_CalAge(DOB) AS
(
SELECT '07/16/1997'
UNION ALL
SELECT '09/04/2013'
)
SELECT
DOB
, CAST(ROUND(DATEDIFF(dd, DOB, GETDATE())/365.0, 2) as DECIMAL(10,2))
, CASE
WHEN  CAST(DATEDIFF(dd, DOB, GETDATE())/365.0 as DECIMAL(10,2))  > 1 THEN  CAST(CAST(DATEDIFF(dd, DOB, GETDATE())/365 as DECIMAL(10,0)) as VARCHAR(5))
ELSE  CAST(CAST(DATEDIFF(dd, DOB, GETDATE())/365.0 as DECIMAL(10,2)) as VARCHAR(5))
END as Age
FROM CTE_CalAge
```

Abhi.

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Wednesday, August 27, 2014 4:04 PM
• Try This Script it will give you actual Age :

Declare @Date Date
set @Date = '07/16/1997'

Select
Convert ( varchar(1500) ,  ( DATEDIFF (s,@Date,GetDate() ) ) /(86400 * 365) ) + ' Day'  + ' '
+ Convert ( varchar(1500) ,  ( DATEDIFF (s,@Date,GetDate() ) ) % 86400/(3600) ) + ' Hour' + ' '
+ Convert ( varchar(1500) ,  ( DATEDIFF (s,@Date,GetDate() ) ) % 3600/60 ) + ' Min'  + ' '   AS 'AGE'

Thanks
• Edited by Wednesday, August 27, 2014 4:17 PM
Wednesday, August 27, 2014 4:16 PM
• Q: What is the first rule of any tiered architecture?

A: display formatting is done in a presentation layer, not in a query!!

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Wednesday, August 27, 2014 7:54 PM
• Why 0.98?

DECLARE @d1 DATETIME,@d2 DATETIME;
SELECT @d1='19970716',@d2='20140828';

SELECT (0+CONVERT(CHAR(8),@d2,112)-CONVERT(CHAR(8),@d1,112))/10000;

SELECT @d1='20140904',@d2='20140828';

SELECT (0+CONVERT(CHAR(8),@d2,112)-CONVERT(CHAR(8),@d1,112))/10000.0;

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Thursday, August 28, 2014 7:18 AM
• I'm not sure how accurate you need to be but here's one option.

```with cteDOB
as
(
select CAST('8/26/1969' as date) [DOB]
union all
select CAST('9/24/1967' as date) [DOB]
union all
select CAST('6/27/1941' as date) [DOB]
union all
select CAST('6/5/2014' as date) [DOB]
)
select
dob
,DATEDIFF(year,dob,sysdatetime()) [yrs]
,DATEDIFF(month,dob,sysdatetime())%12 [mnth]
,DATEDIFF(year,dob,sysdatetime())+((DATEDIFF(month,dob,sysdatetime())%12)/12.0) [raw age]
,case sign(datediff(year,dob,sysdatetime()))
when 1
then DATEDIFF(year,dob,sysdatetime())

else
((DATEDIFF(month,dob,sysdatetime())%12)/12.0)
end [age in years]
from cteDOB;   ```

Thursday, August 28, 2014 10:40 AM