Microsoft KB Archive/45642

PRSQL9012001: DATEDIFF Overflows If Less Than One Day ID Number: Q45642

1.10 OS/2 buglist1.10

Summary:

PROBLEM ID: PRSQL9012001

SYMPTOMS Using the DATEDIFF command with the minute datepart returns the following message:

  Msg 535, Level 16, State 0:
  Difference of two datetime fields caused overflow at runtime.

CAUSE An overflow occurs with SQL Server if DATEDIFF is requested in minutes, seconds, or milliseconds (but not hours) between two “datetime” fields with different date values, and if the first “datetime” field is later in the day than the second “datetime” field. For example, the following command illustrates this problem:

  SELECT DATEDIFF( MINUTE, "JAN 1 1990 1:30PM","JAN 10 1990 1:29PM")

WORKAROUND The following stored procedure produces the correct results:

  create procedure mydiff @date1 datetime, @date2 datetime
  as
  select
      (datediff( mi,
             convert(char(12),@date1,7),
             convert(char(12),@date2,7)
               )
      )  
      +
     (datediff( mi,
            right(@date1,8),
            right(@date2,8)
              )  
      )

STATUS Microsoft has confirmed this to be a problem in SQL Server version 1.1. We are researching this problem and will post new information here as it becomes available.

Additional reference words: 1.10