• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1229
  • Last Modified:

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

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
wkrasner
Asked:
wkrasner
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Surendra NathTechnology LeadCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
wkrasnerAuthor Commented:
worked like a charmer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now