Solved

SQL stored Procedure returning a Difference of two datetime columns caused overflow at runtime

Posted on 2014-01-31
4
1,006 Views
Last Modified: 2014-02-01
The following sp results in overflow error.  The dates in the datediff are passed as '1/1/1900' if null , so I am at a loss:

ALTER FUNCTION [dbo].[Multiload] 
(@vehicle varchar(10), @acknowledged datetime, @atscene datetime, @transporting datetime,@atdest datetime, @enroute datetime)
RETURNS int
AS
BEGIN
declare @Return int
select @return =  (select count(Vehname)   from rep.dbo.CAD_TRIPS_PRIMARY_MAV_TRANSPORTS
where 
	(
abs(datediff(s,@acknowledged,acknowledged))<=10

and

	abs(datediff(s,@enroute,enroute))<=10

and
vehname=@vehicle

and

((atscene <= @atscene and atdest <=@atdest ) or (@atscene <= atscene and @atdest <=atdest ) )


	)
)
return @return
end

Open in new window

0
Comment
Question by:wkrasner
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39824912
http://msdn.microsoft.com/library/ms189794.aspx
datediff returns INT, so if you are using "s" => second, this is likely to give indeed a overflow if the dates are really too far away.
so you must use something else, either minutes, hours or another reference date.
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39824929
It is not possible to do it directly....

but there is an other way

First step create this function in your database

Create Function dbo.DateDiff_BigntSecond
(@FromDate DATETIME,@ToDate DATETIME)
RETURNS BIGINT
AS
BEGIN
    --SET @FromDate = '1/1/1990'
    --SET @ToDate = '1/1/9999'
    DECLARE @BeforeDayOfToDate DATETIME
    SET @BeforeDayOfToDate = DATEADD(day,-1,@todate)
    DECLARE @NumberOfDays BIGINT
    SET @NumberOfDays = DATEDIFF(dd,@FromDate,@BeforeDayOfToDate)
    DECLARE @NumberOfSeconds BIGINT
    SET @NumberOfSeconds = DATEDIFF(ss,@BeforeDayOfToDate,@ToDate)
    RETURN (@NumberOfDays*3600) + @NumberOfSeconds
END

Open in new window


Now you can re-write your code as below

ALTER FUNCTION [dbo].[Multiload] 
(@vehicle varchar(10), @acknowledged datetime, @atscene datetime, @transporting datetime,@atdest datetime, @enroute datetime)
RETURNS int
AS
BEGIN
declare @Return int
select @return =  (
select count(Vehname)   from rep.dbo.CAD_TRIPS_PRIMARY_MAV_TRANSPORTS
where 
	(
abs(DateDiff_BigntSecond(@acknowledged,acknowledged))<=10

and

	abs(DateDiff_BigntSecond(s,@enroute,enroute))<=10

and
vehname=@vehicle

and

((atscene <= @atscene and atdest <=@atdest ) or (@atscene <= atscene and @atdest <=atdest ) )
	)
)
return @return
end

Open in new window


now you can successfully override the overflow limitation.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39825589
Since for the difference to be less than or equal to 10 secs, it must also be less than or equal to 1 minute (else the diff would be at least 61 secs), and since the difference in minutes will realistically never overflow, I suggest this:


ALTER FUNCTION [dbo].[Multiload]
(@vehicle varchar(10), @acknowledged datetime, @atscene datetime, @transporting datetime,@atdest datetime, @enroute datetime)
RETURNS int
AS
BEGIN
RETURN (
    select count(Vehname)
    from rep.dbo.CAD_TRIPS_PRIMARY_MAV_TRANSPORTS
    where (
        1 = CASE WHEN abs(datediff(minute,@acknowledged,acknowledged)) <= 1
                        THEN CASE WHEN abs(datediff(second,@acknowledged,acknowledged))<=10 THEN 1
                 ELSE 0 END ELSE 0 END
    and
        1 = CASE WHEN abs(datediff(minute,@enroute,enroute)) <= 1
                       THEN CASE WHEN abs(datediff(second,@enroute,enroute))<=10 THEN 1
                 ELSE 0 END ELSE 0 END

    and
        vehname=@vehicle
    and
        ( (atscene <= @atscene and atdest <=@atdest ) or (@atscene <= atscene and @atdest <=atdest ) )
      )
)
END


Btw, using "CASE" instead of just AND between the two DATEDIFF conditions prevents SQL from "looking ahead" / "pre-testing" the "abs(datediff(second..." and having it fail even if the difference is > 1 minute.


>> It is not possible to do it directly.... <<

I think it is possible to do it more directly than that :-) .
0
 
LVL 5

Author Closing Comment

by:wkrasner
ID: 39826143
worked like a charmer
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question