Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Calculating age Expand / Collapse
Author
Message
Posted Friday, September 20, 2013 3:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:11 AM
Points: 24, Visits: 1,182
This seems to work



DECLARE @DOB DATETIME

SET @DOB='9/19/2000'


SELECT FLOOR(DATEDIFF(DD,@DOB,GETDATE())/365.25)

Post #1497062
Posted Friday, September 20, 2013 3:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
gilbert delarosa (9/20/2013)
This seems to work



DECLARE @DOB DATETIME

SET @DOB='9/19/2000'


SELECT FLOOR(DATEDIFF(DD,@DOB,GETDATE())/365.25)



Good try but it doesn't work...

DECLARE @DOB DATETIME

SET @DOB='01/01/2013'

SELECT FLOOR(DATEDIFF(DD,@DOB,'01/01/2014')/365.25)

Results:
---------------------------------------
0

(1 row(s) affected)


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497067
Posted Friday, September 20, 2013 4:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:11 AM
Points: 24, Visits: 1,182
But they wouldn't be 1 until after their BDay
or you could just
DECLARE @DOB DATETIME

SET @DOB='01/01/2013'

SELECT FLOOR((DATEDIFF(DD,@DOB,'01/01/2014')+1)/365.25)

Post #1497069
Posted Friday, September 20, 2013 5:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
gilbert delarosa (9/20/2013)
But they wouldn't be 1 until after their BDay
or you could just
DECLARE @DOB DATETIME

SET @DOB='01/01/2013'

SELECT FLOOR((DATEDIFF(DD,@DOB,'01/01/2014')+1)/365.25)



Still doesn't work. Last I heard, you gained a year on your birthday and not the day before (Feb 29 babies sometimes excluded).

DECLARE @DOB DATETIME

SET @DOB='01/01/2012'

SELECT FLOOR((DATEDIFF(DD,@DOB,'12/31/2012')+1)/365.25)


Results:

---------------------------------------
1

(1 row(s) affected)



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497080
Posted Friday, September 20, 2013 6:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:19 AM
Points: 1,805, Visits: 5,871
declare @dob datetime,@now date

set @dob='1 jan 2012'
set @now = '31 dec 2012'

select
datepart(year,@now) - datepart(year,@dob)
+
case
when dateadd(day
,datepart(day,@dob)-1
,dateadd(month
,datepart(month,@dob)-1
,dateadd(year
,datepart(year,@now)-1800
,'1 jan 1800'
)
)
) > @now
then -1
else 0
end



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1497088
    « Prev Topic | Next Topic »

    Add to briefcase ««12

    Permissions Expand / Collapse