Error on DATEDIFF calculation

• SSSolice

  SSCommitted

  Points: 1907

  More actions

  April 8, 2010 at 12:22 pm

  #139343

  Performing this datediff I am getting "Difference of two datetime columns caused overflow at runtime." when one or more values is NULL. Can someone please show me the best way or even different ways to handle this. I need to insert logic so that in the case when either one value or the other is NULL I want to simply NOT perform the calculation (and make the column equal to "exception" or 0 or anything.

  SELECT .......

  ...,'BullPen_GateIn' = ABS(CONVERT(BIGINT,DateDiff(ss,

  CASEWHENmpp.LoadArriveTSISNULLOR

  mpp.LoadArriveTS<@BD-30OR

  mpp.LoadArriveTS>@ED+30

  THEN0

  ELSEmpp.LoadArriveTS

  END

  ,CASEWHENmpp.LoadReadyTSISNULLOR

  mpp.LoadReadyTS<@BD-30OR

  mpp.LoadReadyTS>@ED+30

  THEN0

  ELSEmpp.LoadReadyTS

  END)))

  Thanks Very Much!

• Lowell

  SSC Guru

  Points: 323506

  More actions

  April 8, 2010 at 12:27 pm

  #1148553

  the issue is data related; you are going for a diff in seconds; the max value is an integer(2147483648 or unsigned 4294967296?)

  but the difference between the two dates is bigger than an int, so you get the error;

  the biggest diff in seconds you can have is 68 years or so.

  i believe the "THEN 0" as the case statment converts to 01/01/1900, so any date later than 1968-01-20 03:14:07.000 will raise the error.

  do you HAVE to go to the diff in seconds? why not the diff in minutes, then multiply by 60 to make your big int final results;

  'BullPen_GateIn' = ABS(CONVERT(BIGINT,DateDiff(mm,

  CASE WHEN mpp.LoadArriveTS IS NULL OR

  mpp.LoadArriveTS < @BD-30 OR

  mpp.LoadArriveTS > @ED+30

  THEN 0

  ELSE mpp.LoadArriveTS

  END

  , CASE WHEN mpp.LoadReadyTS IS NULL OR

  mpp.LoadReadyTS < @BD-30 OR

  mpp.LoadReadyTS > @ED+30

  THEN 0

  ELSE mpp.LoadReadyTS

  END) * 60 --making the int of minutes into seconds

  ))

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• SSSolice

  SSCommitted

  Points: 1907

  More actions

  April 8, 2010 at 12:40 pm

  #1148564

  Thanks. I suppose I don't have to go to seconds, but my hh:mm:ss.mmm conversion functions (report-side) are already built and based on seconds. However, that aside, my problem centers around dealing with bad data and wanting to illustrate or highlight for the customer all exceptions (i.e 12/31/1899, NULL, etc) in the data they are providing when I do the reporting along with frequencies of exceptions across many date-time-stamp columns.

  So when I hit one of these exceptions, I want to count it but then bail out of doing the calculation that follows.

  Thanks again for any suggestions.

• K Cline

  Ten Centuries

  Points: 1391

  More actions

  April 8, 2010 at 12:56 pm

  #1148581

  Instead of trying to make the values safe during the calculation, test to see if they will work before doing the calculation. If not, simply return a null or some default value.

  Select ...., Case

  When (COALESCE(mpp.LoadArriveTS, 0) Between @BD-30 And @ED+30)

  And (COALESCE(mpp.LoadReadyTS, 0) Between @BD-30 And @ED+30)

  Then

  -- Working with "safe values", do the calculation.

  ABS(CONVERT(BIGINT,DATEDIFF(mm, mpp.LoadArriveTS, mpp.LoadReadyTS))) * 60

  Else Null

  End As [BullPen_GateIn], ...

  From ...

• SSSolice

  SSCommitted

  Points: 1907

  More actions

  April 8, 2010 at 1:29 pm

  #1148616

  Works perfectly! Thanks K.