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