DateDiff strangeness

• SW_Lindsay

  SSCommitted

  Points: 1983

  More actions

  November 18, 2009 at 12:30 pm

  #216821

  A developer altered me to the fact that

  select DATEDIFF(year,'2009-12-31 23:59:59.997', '2010-01-01 00:00:00.000')

  returns 1.

  Any across year datediff does this regardless of how close to year change it is.

  select DATEDIFF(MILLISECOND,'2009-12-31 23:59:59.997', '2010-01-01 00:00:00.000')

  returns 4

  Anyone ever run into this? It is the same on SQL Server 2000 , 2005, and 2008.

  Steve

• GSquared

  SSC Guru

  Points: 260824

  More actions

  November 18, 2009 at 12:33 pm

  #1081135

  Yeah, that's what DateDiff does. It's how it's supposed to work. Always has been.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• LutzM

  SSC Guru

  Points: 107049

  More actions

  November 18, 2009 at 4:03 pm

  #1081283

  SELECT DATEDIFF(year,'2009-12-31 23:59:59.997', '2010-01-01 00:00:00.000')

  basically means

  SELECT DATEPART(YEAR,2nd value) - DATEPART(YEAR,1st value) -- which is 2010 - 2009 = 1

  If you'd like to get difference displayed as "years", but measured in seconds you would need to do some math. But this would require to define how long a year is for this specific scenario. Let's assume you set 1 year = 365 days.

  Then you could play around getting the following results:

  select DATEDIFF(year,'2009-12-31 23:59:59.997', '2010-01-01 00:00:00.000')*1.00 / 1

  select DATEDIFF(month,'2009-12-31 23:59:59.997', '2010-01-01 00:00:00.000')*1.00 / 12

  select DATEDIFF(day,'2009-12-31 23:59:59.997', '2010-01-01 00:00:00.000')*1.00 / 365

  select DATEDIFF(hour,'2009-12-31 23:59:59.997', '2010-01-01 00:00:00.000')*1.00 / 8760

  select DATEDIFF(minute,'2009-12-31 23:59:59.997', '2010-01-01 00:00:00.000')*1.00 / 525600

  select DATEDIFF(second,'2009-12-31 23:59:59.997', '2010-01-01 00:00:00.000')*1.00 / 31536000

  select DATEDIFF(millisecond,'2009-12-31 23:59:59.997', '2010-01-01 00:00:00.000')*1.00 / 31536000000

  All statements will have the same input and all are "normalized" to a one year base (=365 days). But the results a "slightly" different. And that's because they are intended to be...

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]