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

All replies

• 

  

  0

  Sign in to vote

  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

  
  

  • Edited by Bodo Michael Danitz Wednesday, August 27, 2014 3:18 PM

  Wednesday, August 27, 2014 3:15 PM
• 

  

  0

  Sign in to vote

  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
• 

  

  1

  Sign in to vote

  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
• 

  

  1

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
  

  • Proposed as answer by Simon_HouMicrosoft contingent staff Thursday, August 28, 2014 6:38 AM

  Wednesday, August 27, 2014 4:03 PM
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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 Zeeshan Wednesday, August 27, 2014 4:17 PM

  Wednesday, August 27, 2014 4:16 PM
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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

  Answerer
• 

  

  0

  Sign in to vote

  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;   

  
  

  
  
  

  • Edited by George Robertson Jr Thursday, August 28, 2014 10:54 AM
  • Proposed as answer by George Robertson Jr Saturday, August 30, 2014 9:32 AM
  • Marked as answer by Charlie Liao Friday, September 5, 2014 7:28 AM

  Thursday, August 28, 2014 10:40 AM