Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 21st, 2003, 02:31 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default Age Calculation

Does anyone know an elegant way to calculate age (to the day)? DateDiff(yyyy, startdate, enddate) doesn't quite work.

Rand
__________________
Rand
Reply With Quote
  #2 (permalink)  
Old November 21st, 2003, 03:35 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

 How can I calculate someone's age in SQL Server?

Select CASE
WHEN dateadd(year, datediff (year, d1, d2), d1) > d2
THEN datediff (year, d1, d2) - 1
ELSE datediff (year, d1, d2)
END as Age

Reply With Quote
  #3 (permalink)  
Old November 21st, 2003, 03:38 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hmm, maybe you can cast both dates to int and then subtract start from end. That should give the number of days difference between them (though it won't be in the form x years, y months, z days - it'll just be t days)

(dates are actually stored as floats, where the int part is the days and the decimal part is the time)

hth
Phil
Reply With Quote
  #4 (permalink)  
Old November 21st, 2003, 04:00 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by pgtips


(dates are actually stored as floats, where the int part is the days and the decimal part is the time)
Actually, that's not true.

DateTime datatypes are stored as a two part integer (hex value, actually). The first part is the (signed) number of days since 1/1/1900, and the second is the number of clock ticks since midnight on that day. A clock tick is 3.33 milliseconds. For the datetime datatype, each part is 4 bytes; for smalldatetime each part is two bytes (and the clock ticks are in minutes, and the number of days is not signed).

You may be thinking of a programming language like VB, where the Date datatype is indeed represented as a floating point number (double). In SQL server, it's not stored that way.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #5 (permalink)  
Old November 21st, 2003, 04:21 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

Using Northwind Database here is another example


USE northwind
GO
SELECT DATEDIFF(year, BirthDate, getdate()) AS Age
FROM Employees
GO


Reply With Quote
  #6 (permalink)  
Old November 21st, 2003, 05:37 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

That won't work, as the original poster observed.

Consider what would happen if you ran your query on, say, Jan 10, 2004, with a value of Nov 21, 2003. The DATEDIFF function would return 1 as an age, which is incorrect. The reason you get a 1 is that the DATEDIFF function counts the number of date boundaries crossed, and for years, the date boundary is Jan 1. So even though the age is only about 2 months, the function would incorrectly report the age as 1 year, since Jan 1 was crossed.

The prior, more complex expression you posted is the correct way, I think.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #7 (permalink)  
Old November 21st, 2003, 05:52 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

Thank you for point that out, Greatly appreciated.

Reply With Quote
  #8 (permalink)  
Old November 21st, 2003, 06:10 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

 
Quote:
quote:for smalldatetime each part is two bytes (and the clock ticks are in minutes, and the number of days is not signed).
Quote:
I assume then that the smalldatetime will fail (out of range) on June 07, 2079.

Rand
Reply With Quote
  #9 (permalink)  
Old November 21st, 2003, 06:47 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

DateDiff is one of those tricky functions that looks simple but isn't.

SELECT DateDiff(dd, '12/31/2003', '01/01/2004') returns 1 (day)
SELECT DateDiff(mm, '12/31/2003', '01/01/2004') also returns 1 (month)
SELECT DateDiff(yyyy, '12/31/2003', '01/01/2004') also returns 1 (year)

Age calculations (to the day) are so common that there should be a built in function AgeOn(DateOfBirth, ReferenceDate) that returns the number of full years that have passed as of the ReferenceDate. I guess I'll have to write that one myself.

Another handy one would be a DateFrac(datetime) function which would return a datetime datatype with the time set to 00:00:00.

And the converse TimeFrac(datetime) function would return a datetime datatype with the date set to 01/01/1900 (day zero).

Thanks, particularly to jemacc and Jeff Mason for the information. I had hoped that there would be a simpler way to calculate age, but NO SUCH LUCK! The code first suggested by jemacc worked - I even modified it to return a -1 if either of the inputs (d1 or d2) was null.

Rand
Reply With Quote
  #10 (permalink)  
Old November 21st, 2003, 08:00 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by rgerald
I assume then that the smalldatetime will fail (out of range) on June 07, 2079.
The small datetime can represent dates through June 6, 2079; any date after that will bomb out with an overflow error.

(I doubt that very many of us will be around to worry about it, though :D )

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Getting Respondents Age from Dates Juan Patrick Visual Basic 2005 Basics 1 July 3rd, 2007 06:55 AM
age function keyvanjan ASP.NET 1.0 and 1.1 Basics 2 August 30th, 2006 08:46 AM
How to view remaining months using age(timestamp)? ray_noel Forum and Wrox.com Feedback 1 July 17th, 2006 01:33 PM
Vacancy Age alannoble26 Excel VBA 2 November 23rd, 2005 03:05 AM
calculating a persons age Hudson40 Access VBA 4 January 26th, 2005 11:45 AM



All times are GMT -4. The time now is 02:27 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.