Solved

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

Posted on 2014-01-31
4
980 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:ScottPletcher
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

943 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now