Solved

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

Posted on 2014-01-31
4
1,025 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 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 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

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

Suggested Solutions

Title # Comments Views Activity
SQL STANDARD CORE 7 31
Re-appearing SQL Server Agent jobs 7 29
how to just get time from a date 6 32
SQL Quer 4 21
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 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