Age

Question

Post reply

Age

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

valeryk2000 SSCarpal Tunnel Points: 4245 More actions · Answer 1

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' ?

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

valeryk2000 SSCarpal Tunnel Points: 4245 More actions · Answer 3

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 ...

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

valeryk2000 SSCarpal Tunnel Points: 4245 More actions · Answer 5

Right ... it also does not work with infants (before they reach 1 year) ...

Thanks

Dwain Camps SSC Guru Points: 86948 More actions · Answer 6

Old thread but this is my reference article on the subject:

Efficiently Querying and Calculating Using SQL Server Date/Time Columns --by Adam Machanic

Age is covered near the end.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

valeryk2000 SSCarpal Tunnel Points: 4245 More actions · Answer 7

Very good article indeed. Learned a lot. I'm just taking on the "age" problem, it seems more complicated than I initially thought. I am working for a large health system including several hospitals, and age of patients may in years, months, weeks and even days (newborns). So I am going to write a universal function (or set of functions) that would return age in any of those units.

Thanks to everybody

Val

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

valeryk2000 (11/19/2013)
Very good article indeed. Learned a lot. I'm just taking on the "age" problem, it seems more complicated than I initially thought. I am working for a large health system including several hospitals, and age of patients may in years, months, weeks and even days (newborns). So I am going to write a universal function (or set of functions) that would return age in any of those units.
Thanks to everybody
Val

Cool. If you get the chance, post the function(s) when you're done. I'd love to see them. Thanks.