DataWriter

December 10, 2010

A DateDiff Gotcha

Filed under: SQLServerPedia Syndication,T-SQL — Joe Casella @ 10:27 pm
Tags: ,

Today is December 10, 2010.   Suppose that Able was born on December 9, 2000 and Baker was born on December 11, 2000.  How old are Able and Baker?  Able is 10 and Baker is 9.  But if you ask the question in T-SQL as:

  1. SELECT DATEDIFF(year, '12/09/2000', '12/10/2010') AS Able
  2.    , DATEDIFF(year, '12/11/2000', '12/10/2010') AS Baker

You’ll get a different answer—Able and Baker are both 10!  Why?  According to BOL the DATEDIFF function returns the count of “the specified datepart boundaries crossed between the startdate and enddate.”  My sample above uses year as the datepart, so the function returns the number of year boundaries (January 1)  between the two dates.  In this case it’s 10 for both Able and Baker.  The function doesn’t base its calculation on the actual number of days elapsed between the two dates.

If you need their true age (because you’re sending a mailing to the parents of 10 year olds) you need to write your T-SQL in a way that accounts for crossing the datepart boundary before a birthday.  Here’s one way:

  1. DECLARE @Birthdate date
  2. DECLARE @AtDate date
  3. DECLARE @YearsOld int
  4.  
  5.   SET @Birthdate = '12/11/2000'
  6.   SET @AtDate = '12/10/2010'
  7.  
  8.   SELECT @YearsOld = DATEDIFF (YY, @Birthdate, @AtDate)
  9.  
  10.   IF @AtDate < DATEADD (yyyy, @YearsOld, @BirthDate)
  11.     BEGIN
  12.       SELECT @YearsOld = @YearsOld 1
  13.     END
  14.  
  15.   SELECT @YearsOld

Now SQL Server returns 10 for Able and 9 for Baker, just as we expected.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.