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 ««123»»

Age Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 1:43 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 4:07 PM
Points: 37,080, Visits: 31,640
Michael Valentine Jones (9/25/2012)
You can use the function on the link below to find the age:
Age Function F_AGE_IN_YEARS
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462


Any chance of making it NOT a scalar function?


--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 #1364270
Posted Tuesday, September 25, 2012 2:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 11,194, Visits: 11,166
Jeff Moden (9/25/2012)
Careful now... Because of the date conversions to the CHAR datatype, that will be relatively slow.

It is beautiful though - this has long been a favourite of mine (I think it was Rob Farley that first showed it to me).
As far as performance is concerned, well yes it will be slow. But then, all T-SQL solutions will be slow, right?
Just kidding (mostly) - it's a good point that conversion to string is particularly bad. And especially scalar T-SQL UDFs




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1364289
Posted Tuesday, September 25, 2012 3:18 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 3,136, Visits: 11,493
Jeff Moden (9/25/2012)
Michael Valentine Jones (9/25/2012)
You can use the function on the link below to find the age:
Age Function F_AGE_IN_YEARS
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462


Any chance of making it NOT a scalar function?


I originally wrote this for SQL 2000, so be my guest. The code could be greatly simplified too, probably enough to convert it to inline code.

I posted it mainly to give them an example of some code that I know I tested fairly completely.





Post #1364310
Posted Sunday, February 17, 2013 6:59 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:10 PM
Points: 4,576, Visits: 8,349
Jeff Moden (9/25/2012)
Michael Valentine Jones (9/25/2012)
You can use the function on the link below to find the age:
Age Function F_AGE_IN_YEARS
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462


Any chance of making it NOT a scalar function?


SELECT DATEDIFF(yy, 0, GETDATE() - DOB) Age_Way1, 
YEAR(GETDATE()-DOB) -1900 Age_Way2

Post #1421022
Posted Tuesday, February 19, 2013 3:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 187, Visits: 1,150
My two bits...


declare @dob datetime
set @dob = '2/29/2000'

select convert(char(10),@dob,101) as DOB,
case
when ((MONTH(@dob) * 100) + DAY(@dob)) > ((MONTH(getdate()) * 100) + DAY(getdate()))
then DATEDIFF(year,@dob,getdate()) - 1
else DATEDIFF(year,@dob,getdate()) End as AgeInYears
Post #1421879
Posted Monday, November 18, 2013 9:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:06 PM
Points: 148, Visits: 293
Just bumped on this:


DECLARE @dob datetime
SET @dob='1992-01-09 00:00:00'
SELECT DATEDIFF(YEAR, '0:0', getdate()-@dob)



Works ok. Who can explain '0:0' ?
Post #1515295
Posted Monday, November 18, 2013 10:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 13,455, Visits: 12,318
valeryk2000 (11/18/2013)
Just bumped on this:


DECLARE @dob datetime
SET @dob='1992-01-09 00:00:00'
SELECT DATEDIFF(YEAR, '0:0', getdate()-@dob)



Works ok. Who can explain '0:0' ?


Actually it doesn't really work ok. See the posts on the first page.

However, the '0:0' is simply a strange way of writing 1/1/1900 or the 0 date. Remember the second parameter is a datetime so it will perform an implicit conversion.

select cast('0:0' as datetime)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1515298
Posted Monday, November 18, 2013 10:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:06 PM
Points: 148, Visits: 293
Thanks. So simple zero can work as well. So far I checked the code several times - and it was ok. Where is a potential error? May be I missed something on page 1 ...
Post #1515304
Posted Monday, November 18, 2013 1: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 @ 4:07 PM
Points: 37,080, Visits: 31,640
valeryk2000 (11/18/2013)
So far I checked the code several times - and it was ok. Where is a potential error?


Here's one of many places where it fails because of Leap Years...

 DECLARE @dob  datetime
,@now datetime
SELECT @dob = '2000-02-28 00:00:00'
,@now = '2001-02-27 00:00:00'
SELECT DATEDIFF(YEAR, '0:0', @now-@dob)
;




--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 #1515333
Posted Monday, November 18, 2013 2:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:06 PM
Points: 148, Visits: 293
Right ... it also does not work with infants (before they reach 1 year) ...
Thanks
Post #1515349
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse