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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 30, 2015 1:39 PM
Points: 40, Visits: 1,467
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: Today @ 10:54 AM
Points: 37,867, Visits: 34,743
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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 30, 2015 1:39 PM
Points: 40, Visits: 1,467
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: Today @ 10:54 AM
Points: 37,867, Visits: 34,743
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: Yesterday @ 6:02 PM
Points: 1,964, Visits: 6,603
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);


  • 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