July 23, 2012 at 8:50 am
Hi all my package was running good until last weekend where i'm getting the following error
Message
Executed as user: AD\MOSS_BI. ...te Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:00:04 AM Error: 2012-07-23 02:47:49.55 Code: 0x00000000 Source: Get Duration Description: Difference of two datetime columns caused overflow at runtime. End Error Error: 2012-07-23 02:47:49.56 Code: 0xC002F210 Source: Get Duration Execute SQL Task Description: Executing the query "update dbo.FACT_InspectionDetails set SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS( ScheduledDateTime ,RequestedDateTime ,InspectionDateTime )" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:00:04 AM Finished: 2:55:44 AM Elapsed: 3. The step failed.
I have no idea why it failed all of a sudden as nothing was changed. I the looked at the FUNCTION which i think was causing the problem, its an EXECUTE SQL TASK:
update dbo.TABLE_NAMEset SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS( )
ScheduledDateTime ,RequestedDateTime ,InspectionDateTime)
Can anyone please tell me what going wrong here?
Thanks
July 23, 2012 at 8:59 am
SSCNewbee (7/23/2012)
Hi all my package was running good until last weekend where i'm getting the following errorMessage
Executed as user: AD\MOSS_BI. ...te Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:00:04 AM Error: 2012-07-23 02:47:49.55 Code: 0x00000000 Source: Get Duration Description: Difference of two datetime columns caused overflow at runtime. End Error Error: 2012-07-23 02:47:49.56 Code: 0xC002F210 Source: Get Duration Execute SQL Task Description: Executing the query "update dbo.FACT_InspectionDetails set SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS( ScheduledDateTime ,RequestedDateTime ,InspectionDateTime )" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:00:04 AM Finished: 2:55:44 AM Elapsed: 3. The step failed.
I have no idea why it failed all of a sudden as nothing was changed. I the looked at the FUNCTION which i think was causing the problem, its an EXECUTE SQL TASK:
update dbo.TABLE_NAMEset SecondsDuration = dbo.FN_GET_INSPECTION_DURATION_SECONDS( )
ScheduledDateTime ,RequestedDateTime ,InspectionDateTime)
Can anyone please tell me what going wrong here?
Thanks
Sounds like a data issue. The return type of DATEDIFF is an integer. There could be an issue the spread of the dates and you should check this out.
July 23, 2012 at 9:04 am
Thanks Lynn can you please elaborate in detail? This PACKAGE didnt fail until the last weekend. Also can you please tell me what the "dbo.FN_GET_INSPECTION_DURATION_SECONDS( ScheduledDateTime ,RequestedDateTime ,InspectionDateTime)" is suppoded to do?
July 23, 2012 at 9:08 am
SSCNewbee (7/23/2012)
Thanks Lynn can you please elaborate in detail? This PACKAGE didnt fail until the last weekend. Also can you please tell me what the "dbo.FN_GET_INSPECTION_DURATION_SECONDS( ScheduledDateTime ,RequestedDateTime ,InspectionDateTime)" is suppoded to do?
You will have to tell us what dbo.FN_GET_INSPECTION_DURATION_SECONDS( ScheduledDateTime ,RequestedDateTime ,InspectionDateTime) does as this is a user defined function. My guess, without looking at the code for it, is that it does some kind of calculations between the the dates passed.
As for the error, run the following code, you will see what I mean.
DECLARE @Date1 DATETIME,
@Date2 DATETIME;
SET @Date1 = '19000101';
SET @Date2 = GETDATE();
SELECT @Date1, @Date2, DATEDIFF(ss,@Date1,@Date2);
July 23, 2012 at 9:16 am
Thanks Lynn this is the FUNCTION:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ===================================================================================
-- -- Description:Given a unique inspection number, this function returns an integer
-- representing the Inspection's duration in seconds. Follows logic
-- originally incorporated into the "48-Hour Report" in Crystal.
-- ===================================================================================
--
ALTER FUNCTION [dbo].[FN_GET_INSPECTION_DURATION_SECONDS]
(
-- Add the parameters for the function here
@serv_prov_codevarchar(15),
@b1_per_id1varchar(5),
@b1_per_id2varchar(5),
@b1_per_id3varchar(5),
@g6_act_numbigint
)
RETURNS int
AS
BEGIN
--**********************************************************************************************
-- Declare the return variable
--**********************************************************************************************
DECLARE @duration bigint
--**********************************************************************************************
-- Declare all other local variables
--**********************************************************************************************
DECLARE @start_datetime datetime
DECLARE @start_time1 varchar(10)
DECLARE @start_time2 varchar(10)
DECLARE @end_datetime datetime
DECLARE @end_time1 varchar(10)
DECLARE @end_time2 varchar(10)
--**********************************************************************************************
-- Compute the Starting DateTime
--**********************************************************************************************
-- Start by using the SCHEDULED DATE
select
@start_datetime = g6_act_dd,
@start_time1 = ltrim(rtrim(g6_act_t1)),
@start_time2 = ltrim(rtrim(g6_act_t2))
from [AAA].dbo.G6ACTION
where REC_STATUS = 'A'
and SERV_PROV_CODE = @serv_prov_code
and B1_PER_ID1 = @b1_per_id1
and B1_PER_ID2 = @b1_per_id2
and B1_PER_ID3 = @b1_per_id3
and G6_ACT_NUM = @g6_act_num
-- Use REQUESTED DATE if not scheduled
if @start_datetime is null
begin
select
@start_datetime = g6_rec_dd,
@start_time1 = ltrim(rtrim(g6_rec_t1)),
@start_time2 = ltrim(rtrim(g6_rec_t2))
from [AAA].dbo.G6ACTION
where REC_STATUS = 'A'
and SERV_PROV_CODE = @serv_prov_code
and B1_PER_ID1 = @b1_per_id1
and B1_PER_ID2 = @b1_per_id2
and B1_PER_ID3 = @b1_per_id3
and G6_ACT_NUM = @g6_act_num
end
-- Determine the appropriate timestamp to use
-- (skip this section if there is no StartDate)
if @start_datetime is not null
begin
if datepart(hh,@start_datetime)= 0 and datepart(mi,@start_datetime)= 0 and datepart(ss,@start_datetime)= 0
begin
-- extract h/m/s from @start_time2, based on the pattern of the string
-- test if in 00:00:00 format
if PATINDEX('[0-9][0-9]:[0-9][0-9]:[0-9][0-9]', @start_time2) = 1 and @start_time2 <> '00:00:00'
begin
-- if 12AM, only add minutes and seconds
if left(@start_time2,2) = '12' and @start_time1 = 'AM'
begin
--set @start_datetime = dateadd(hh, cast(left(@start_time2, 2) as int), @start_datetime)
set @start_datetime = dateadd(mi, cast(substring(@start_time2, 4, 2) as int), @start_datetime)
set @start_datetime = dateadd(ss, cast(right(@start_time2, 2) as int), @start_datetime)
end
else
begin
set @start_datetime = dateadd(hh, cast(left(@start_time2, 2) as int), @start_datetime)
set @start_datetime = dateadd(mi, cast(substring(@start_time2, 4, 2) as int), @start_datetime)
set @start_datetime = dateadd(ss, cast(right(@start_time2, 2) as int), @start_datetime)
end
-- if PM, may need to convert to 24-hour clock
if @start_time1 = 'PM' and cast(left(@start_time2, 2) as int) < 12
set @start_datetime = dateadd(hh, 12, @start_datetime)
end
-- test if in 00:00 format
else if PATINDEX('[0-9][0-9]:[0-9][0-9]', @start_time2) = 1 and @start_time2 <> '00:00'
begin
-- if 12AM, only add minutes
if (left(@start_time2,2)) = '12' and @start_time1 = 'AM'
set @start_datetime = dateadd(mi, cast(substring(@start_time2, 4, 2) as int), @start_datetime)
else
begin
set @start_datetime = dateadd(hh, cast(left(@start_time2, 2) as int), @start_datetime)
set @start_datetime = dateadd(mi, cast(right(@start_time2, 2) as int), @start_datetime)
end
-- if PM, may need to convert to 24-hour clock
if @start_time1 = 'PM' and cast(left(@start_time2,2) as int) < 12
set @start_datetime = dateadd(hh, 12, @start_datetime)
end
-- test if in 0:00 format
else if PATINDEX('[0-9]:[0-9][0-9]', @start_time2) = 1 and @start_time2 <> '0:00'
begin
set @start_datetime = dateadd(hh, cast(left(@start_time2, 1) as int), @start_datetime)
set @start_datetime = dateadd(mi, cast(substring(@start_time2, 3, 2) as int), @start_datetime)
-- if PM, convert to 24-hour clock
if @start_time1 = 'PM' set @start_datetime = dateadd(hh, 12, @start_datetime)
end
-- otherwise there is no valid timestamp: per DCE, assume 7:30 AM
else
begin
set @start_datetime = dateadd(hh, 7, @start_datetime)
set @start_datetime = dateadd(mi, 30, @start_datetime)
end
end
end
--**********************************************************************************************
-- Compute the Ending DateTime
--**********************************************************************************************
-- Start by using the Completion Date and Completion Time fields
select
@end_datetime = g6_compl_dd,
@end_time1 = g6_compl_t1,
@end_time2 = g6_compl_t2
from [AAA].dbo.G6ACTION
where REC_STATUS = 'A'
and SERV_PROV_CODE = @serv_prov_code
and B1_PER_ID1 = @b1_per_id1
and B1_PER_ID2 = @b1_per_id2
and B1_PER_ID3 = @b1_per_id3
and G6_ACT_NUM = @g6_act_num
-- Determine the appropriate timestamp to use
-- (skip this section if there is no EndDate)
if @end_datetime is not null
begin
if datepart(hh, @end_datetime) = 0 and datepart(mi, @end_datetime) = 0 and datepart(ss, @end_datetime) = 0
begin
-- extract h/m/s from @end_time2, based on the pattern of the string
-- test if in 00:00:00 format
if PATINDEX('[0-9][0-9]:[0-9][0-9]:[0-9][0-9]', @end_time2) = 1 and @end_time2 <> '00:00:00'
begin
-- if 12AM, only add minutes and seconds
if left(@end_time2,2) = '12' and @end_time1 = 'AM'
begin
--set @end_datetime = dateadd(hh, cast(left(@end_time2, 2) as int), @end_datetime)
set @end_datetime = dateadd(mi, cast(substring(@end_time2, 4, 2) as int), @end_datetime)
set @end_datetime = dateadd(ss, cast(right(@end_time2, 2) as int), @end_datetime)
end
else
begin
set @end_datetime = dateadd(hh, cast(left(@end_time2, 2) as int), @end_datetime)
set @end_datetime = dateadd(mi, cast(substring(@end_time2, 4, 2) as int), @end_datetime)
set @end_datetime = dateadd(ss, cast(right(@end_time2, 2) as int), @end_datetime)
end
-- if PM, may need to convert to 24-hour clock
if @end_time1 = 'PM' and cast(left(@end_time2, 2) as int) < 12
set @end_datetime = dateadd(hh, 12, @end_datetime)
end
-- test if in 00:00 format
else if PATINDEX('[0-9][0-9]:[0-9][0-9]', @end_time2) = 1 and @end_time2 <> '00:00'
begin
-- if 12AM, only add minutes
if (left(@end_time2,2)) = '12' and @end_time1 = 'AM'
set @end_datetime = dateadd(mi, cast(substring(@end_time2, 4, 2) as int), @end_datetime)
else
begin
set @end_datetime = dateadd(hh, cast(left(@end_time2, 2) as int), @end_datetime)
set @end_datetime = dateadd(mi, cast(right(@end_time2, 2) as int), @end_datetime)
end
-- if PM, may need to convert to 24-hour clock
if @end_time1 = 'PM' and cast(left(@end_time2,2) as int) < 12
set @end_datetime = dateadd(hh, 12, @end_datetime)
end
-- test if in 0:00 format
else if PATINDEX('[0-9]:[0-9][0-9]', @end_time2) = 1 and @end_time2 <> '0:00'
begin
set @end_datetime = dateadd(hh, cast(left(@end_time2, 1) as int), @end_datetime)
set @end_datetime = dateadd(mi, cast(substring(@end_time2, 3, 2) as int), @end_datetime)
-- if PM, convert to 24-hour clock
if @end_time1 = 'PM' set @end_datetime = dateadd(hh, 12, @end_datetime)
end
-- otherwise there is no valid timestamp: per DCE, assume 4:30 PM
else
begin
set @end_datetime = dateadd(hh, 16, @end_datetime)
set @end_datetime = dateadd(mi, 30, @end_datetime)
end
end
end
--**********************************************************************************************
-- Compute the Duration; remove Weekend/Holiday Time
--**********************************************************************************************
set @duration = datediff(ss, @start_datetime, @end_datetime)
if @duration < 0
set @duration = @duration + dbo.FN_GET_BLACKOUT_SECONDS(@start_datetime, @end_datetime)
else
set @duration = @duration - dbo.FN_GET_BLACKOUT_SECONDS(@start_datetime, @end_datetime)
--**********************************************************************************************
-- Return the result of the function
--**********************************************************************************************
RETURN @duration
END
CAN you please tell me what to change in the function?
Thanks
July 23, 2012 at 9:35 am
Also Lynn when i execute the FUNCTION by itslef it running withput giving any dataflow error.
July 23, 2012 at 9:36 am
SSCNewbee (7/23/2012)
Thanks Lynn this is the FUNCTION:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ===================================================================================
-- -- Description:Given a unique inspection number, this function returns an integer
-- representing the Inspection's duration in seconds. Follows logic
-- originally incorporated into the "48-Hour Report" in Crystal.
-- ===================================================================================
--
ALTER FUNCTION [dbo].[FN_GET_INSPECTION_DURATION_SECONDS]
(
-- Add the parameters for the function here
@serv_prov_codevarchar(15),
@b1_per_id1varchar(5),
@b1_per_id2varchar(5),
@b1_per_id3varchar(5),
@g6_act_numbigint
)
RETURNS int
AS
BEGIN
--**********************************************************************************************
-- Declare the return variable
--**********************************************************************************************
DECLARE @duration bigint
--**********************************************************************************************
-- Declare all other local variables
--**********************************************************************************************
DECLARE @start_datetime datetime
DECLARE @start_time1 varchar(10)
DECLARE @start_time2 varchar(10)
DECLARE @end_datetime datetime
DECLARE @end_time1 varchar(10)
DECLARE @end_time2 varchar(10)
--**********************************************************************************************
-- Compute the Starting DateTime
--**********************************************************************************************
-- Start by using the SCHEDULED DATE
select
@start_datetime = g6_act_dd,
@start_time1 = ltrim(rtrim(g6_act_t1)),
@start_time2 = ltrim(rtrim(g6_act_t2))
from [AAA].dbo.G6ACTION
where REC_STATUS = 'A'
and SERV_PROV_CODE = @serv_prov_code
and B1_PER_ID1 = @b1_per_id1
and B1_PER_ID2 = @b1_per_id2
and B1_PER_ID3 = @b1_per_id3
and G6_ACT_NUM = @g6_act_num
-- Use REQUESTED DATE if not scheduled
if @start_datetime is null
begin
select
@start_datetime = g6_rec_dd,
@start_time1 = ltrim(rtrim(g6_rec_t1)),
@start_time2 = ltrim(rtrim(g6_rec_t2))
from [AAA].dbo.G6ACTION
where REC_STATUS = 'A'
and SERV_PROV_CODE = @serv_prov_code
and B1_PER_ID1 = @b1_per_id1
and B1_PER_ID2 = @b1_per_id2
and B1_PER_ID3 = @b1_per_id3
and G6_ACT_NUM = @g6_act_num
end
-- Determine the appropriate timestamp to use
-- (skip this section if there is no StartDate)
if @start_datetime is not null
begin
if datepart(hh,@start_datetime)= 0 and datepart(mi,@start_datetime)= 0 and datepart(ss,@start_datetime)= 0
begin
-- extract h/m/s from @start_time2, based on the pattern of the string
-- test if in 00:00:00 format
if PATINDEX('[0-9][0-9]:[0-9][0-9]:[0-9][0-9]', @start_time2) = 1 and @start_time2 <> '00:00:00'
begin
-- if 12AM, only add minutes and seconds
if left(@start_time2,2) = '12' and @start_time1 = 'AM'
begin
--set @start_datetime = dateadd(hh, cast(left(@start_time2, 2) as int), @start_datetime)
set @start_datetime = dateadd(mi, cast(substring(@start_time2, 4, 2) as int), @start_datetime)
set @start_datetime = dateadd(ss, cast(right(@start_time2, 2) as int), @start_datetime)
end
else
begin
set @start_datetime = dateadd(hh, cast(left(@start_time2, 2) as int), @start_datetime)
set @start_datetime = dateadd(mi, cast(substring(@start_time2, 4, 2) as int), @start_datetime)
set @start_datetime = dateadd(ss, cast(right(@start_time2, 2) as int), @start_datetime)
end
-- if PM, may need to convert to 24-hour clock
if @start_time1 = 'PM' and cast(left(@start_time2, 2) as int) < 12
set @start_datetime = dateadd(hh, 12, @start_datetime)
end
-- test if in 00:00 format
else if PATINDEX('[0-9][0-9]:[0-9][0-9]', @start_time2) = 1 and @start_time2 <> '00:00'
begin
-- if 12AM, only add minutes
if (left(@start_time2,2)) = '12' and @start_time1 = 'AM'
set @start_datetime = dateadd(mi, cast(substring(@start_time2, 4, 2) as int), @start_datetime)
else
begin
set @start_datetime = dateadd(hh, cast(left(@start_time2, 2) as int), @start_datetime)
set @start_datetime = dateadd(mi, cast(right(@start_time2, 2) as int), @start_datetime)
end
-- if PM, may need to convert to 24-hour clock
if @start_time1 = 'PM' and cast(left(@start_time2,2) as int) < 12
set @start_datetime = dateadd(hh, 12, @start_datetime)
end
-- test if in 0:00 format
else if PATINDEX('[0-9]:[0-9][0-9]', @start_time2) = 1 and @start_time2 <> '0:00'
begin
set @start_datetime = dateadd(hh, cast(left(@start_time2, 1) as int), @start_datetime)
set @start_datetime = dateadd(mi, cast(substring(@start_time2, 3, 2) as int), @start_datetime)
-- if PM, convert to 24-hour clock
if @start_time1 = 'PM' set @start_datetime = dateadd(hh, 12, @start_datetime)
end
-- otherwise there is no valid timestamp: per DCE, assume 7:30 AM
else
begin
set @start_datetime = dateadd(hh, 7, @start_datetime)
set @start_datetime = dateadd(mi, 30, @start_datetime)
end
end
end
--**********************************************************************************************
-- Compute the Ending DateTime
--**********************************************************************************************
-- Start by using the Completion Date and Completion Time fields
select
@end_datetime = g6_compl_dd,
@end_time1 = g6_compl_t1,
@end_time2 = g6_compl_t2
from [AAA].dbo.G6ACTION
where REC_STATUS = 'A'
and SERV_PROV_CODE = @serv_prov_code
and B1_PER_ID1 = @b1_per_id1
and B1_PER_ID2 = @b1_per_id2
and B1_PER_ID3 = @b1_per_id3
and G6_ACT_NUM = @g6_act_num
-- Determine the appropriate timestamp to use
-- (skip this section if there is no EndDate)
if @end_datetime is not null
begin
if datepart(hh, @end_datetime) = 0 and datepart(mi, @end_datetime) = 0 and datepart(ss, @end_datetime) = 0
begin
-- extract h/m/s from @end_time2, based on the pattern of the string
-- test if in 00:00:00 format
if PATINDEX('[0-9][0-9]:[0-9][0-9]:[0-9][0-9]', @end_time2) = 1 and @end_time2 <> '00:00:00'
begin
-- if 12AM, only add minutes and seconds
if left(@end_time2,2) = '12' and @end_time1 = 'AM'
begin
--set @end_datetime = dateadd(hh, cast(left(@end_time2, 2) as int), @end_datetime)
set @end_datetime = dateadd(mi, cast(substring(@end_time2, 4, 2) as int), @end_datetime)
set @end_datetime = dateadd(ss, cast(right(@end_time2, 2) as int), @end_datetime)
end
else
begin
set @end_datetime = dateadd(hh, cast(left(@end_time2, 2) as int), @end_datetime)
set @end_datetime = dateadd(mi, cast(substring(@end_time2, 4, 2) as int), @end_datetime)
set @end_datetime = dateadd(ss, cast(right(@end_time2, 2) as int), @end_datetime)
end
-- if PM, may need to convert to 24-hour clock
if @end_time1 = 'PM' and cast(left(@end_time2, 2) as int) < 12
set @end_datetime = dateadd(hh, 12, @end_datetime)
end
-- test if in 00:00 format
else if PATINDEX('[0-9][0-9]:[0-9][0-9]', @end_time2) = 1 and @end_time2 <> '00:00'
begin
-- if 12AM, only add minutes
if (left(@end_time2,2)) = '12' and @end_time1 = 'AM'
set @end_datetime = dateadd(mi, cast(substring(@end_time2, 4, 2) as int), @end_datetime)
else
begin
set @end_datetime = dateadd(hh, cast(left(@end_time2, 2) as int), @end_datetime)
set @end_datetime = dateadd(mi, cast(right(@end_time2, 2) as int), @end_datetime)
end
-- if PM, may need to convert to 24-hour clock
if @end_time1 = 'PM' and cast(left(@end_time2,2) as int) < 12
set @end_datetime = dateadd(hh, 12, @end_datetime)
end
-- test if in 0:00 format
else if PATINDEX('[0-9]:[0-9][0-9]', @end_time2) = 1 and @end_time2 <> '0:00'
begin
set @end_datetime = dateadd(hh, cast(left(@end_time2, 1) as int), @end_datetime)
set @end_datetime = dateadd(mi, cast(substring(@end_time2, 3, 2) as int), @end_datetime)
-- if PM, convert to 24-hour clock
if @end_time1 = 'PM' set @end_datetime = dateadd(hh, 12, @end_datetime)
end
-- otherwise there is no valid timestamp: per DCE, assume 4:30 PM
else
begin
set @end_datetime = dateadd(hh, 16, @end_datetime)
set @end_datetime = dateadd(mi, 30, @end_datetime)
end
end
end
--**********************************************************************************************
-- Compute the Duration; remove Weekend/Holiday Time
--**********************************************************************************************
set @duration = datediff(ss, @start_datetime, @end_datetime)
if @duration < 0
set @duration = @duration + dbo.FN_GET_BLACKOUT_SECONDS(@start_datetime, @end_datetime)
else
set @duration = @duration - dbo.FN_GET_BLACKOUT_SECONDS(@start_datetime, @end_datetime)
--**********************************************************************************************
-- Return the result of the function
--**********************************************************************************************
RETURN @duration
END
CAN you please tell me what to change in the function?
Thanks
No, I can't tell you what to change. The first thing I would do is actually look at the data. If everything has been working until now, the problem may be the data.
July 23, 2012 at 9:58 am
Lynn,
what should i look for in the data as all the dates are in a valid date format.
Thanks
July 23, 2012 at 10:10 am
SSCNewbee (7/23/2012)
Lynn,what should i look for in the data as all the dates are in a valid date format.
Thanks
I can't tell you that, I don't know your system nor what the state of the data was when the process aborted. All I can tell you is from what I see in the error message, this is not a code problem until you prove that it isn't a data problem.
If it is a code problem, it is likely a logic error based on the data processed at the time. Something that was not accounted for during development or testing of the code. Again, you need to look at the data.
Looking at the code, I think there is a lot being done that doesn't need to be done. It is possibile that this whole function could be rewritten. It also uses another function internally that I would look at rewriting if I rewrote this function.
July 23, 2012 at 10:20 am
Thanks Lynn, i figured out that the problem is with the datediff function, where it is being more precise and hence when comparing the 3 sifferent dates its resulting in negative.
set @duration = datediff(ss, @start_datetime, @end_datetime).I'm thinking that the followingmessage might be helpful
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
But i need seconds so how can i get a less precise datepart?
Thanks
July 23, 2012 at 10:24 am
The data looks like this
scheduleddatetime requesteddatetime inspecteddatetime
2012-07-23 07:30:00.0002012-07-23 09:27:00.0002012-07-23 09:27:26.533
2012-07-23 07:30:00.0002012-07-23 09:27:00.0002012-07-23 09:27:27.033
2012-07-23 07:30:00.0002012-07-21 07:30:00.0002012-07-21 17:28:26.733
2012-07-23 07:30:00.0002012-07-23 09:27:00.0002012-07-23 09:27:27.423
2012-07-23 07:30:00.0002012-07-23 09:27:00.0002012-07-23 09:27:29.873
2012-07-23 07:30:00.0002012-07-23 09:27:00.0002012-07-23 09:27:30.607
2012-07-23 07:30:00.0002012-07-23 09:27:00.0002012-07-23 09:27:31.010
July 23, 2012 at 10:26 am
sample data
scheduledatetimerequesteddatetimeinspecteddatetime
7/23/12 7:30 AM7/23/12 9:27 AM7/23/12 9:27 AM
7/23/12 7:30 AM7/23/12 9:27 AM7/23/12 9:27 AM
7/23/12 7:30 AM7/21/12 7:30 AM7/21/12 5:28 PM
7/23/12 7:30 AM7/23/12 9:27 AM7/23/12 9:27 AM
7/23/12 7:30 AM7/23/12 9:27 AM7/23/12 9:27 AM
7/23/12 7:30 AM7/23/12 9:27 AM7/23/12 9:27 AM
7/23/12 7:30 AM7/23/12 9:27 AM7/23/12 9:27 AM
July 23, 2012 at 10:31 am
SSCNewbee (7/23/2012)
The data looks like thisscheduleddatetime requesteddatetime inspecteddatetime
2012-07-23 07:30:00.0002012-07-23 09:27:00.0002012-07-23 09:27:26.533
2012-07-23 07:30:00.0002012-07-23 09:27:00.0002012-07-23 09:27:27.033
2012-07-23 07:30:00.0002012-07-21 07:30:00.0002012-07-21 17:28:26.733
2012-07-23 07:30:00.0002012-07-23 09:27:00.0002012-07-23 09:27:27.423
2012-07-23 07:30:00.0002012-07-23 09:27:00.0002012-07-23 09:27:29.873
2012-07-23 07:30:00.0002012-07-23 09:27:00.0002012-07-23 09:27:30.607
2012-07-23 07:30:00.0002012-07-23 09:27:00.0002012-07-23 09:27:31.010
Looking at the data above, and running the following code, I don't see how the data above is causing a data overflow.
DECLARE @myint INT;
SET @myint = 2147483647;
SELECT DATEADD(ss,@myint,GETDATE()),DATEADD(ss,-1 * @myint,GETDATE())
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply