Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-01-31
4
Medium Priority
?
1,154 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 143

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 2000 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 70

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

609 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