Description: Difference of two datetime columns ca

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Description: Difference of two datetime columns ca

Author

Topic

thinkingeye
Starting Member

7 Posts

Posted - 2012-07-23 : 10:42:53

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 12:05:59

that issue is data. you've some date value coming in data which when passed to function and when you apply date function (probably datediff) its causing return value to overflow the allowable value limit.

Open package in BIDS and run it for current data and store the records on to a table created in sql db. then inside table using a select query check case where value is going beyond limit when passed to function and you should get spurious data which caused issue. correct it in source and then run package again.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thinkingeye
Starting Member

7 Posts

Posted - 2012-07-23 : 12:51:04

Thanks Viskh i have the resulting dataset as

scheduledtime     requestedtime        inspectedtime
2012-07-23 07:30:00.000	2012-07-23 09:27:00.000	2012-07-23 09:27:26.533
2012-07-23 07:30:00.000	2012-07-23 09:27:00.000	2012-07-23 09:27:27.033
2012-07-23 07:30:00.000	2012-07-21 07:30:00.000	2012-07-21 17:28:26.733
2012-07-23 07:30:00.000	2012-07-23 09:27:00.000	2012-07-23 09:27:27.423
2012-07-23 07:30:00.000	2012-07-23 09:27:00.000	2012-07-23 09:27:29.873
2012-07-23 07:30:00.000	2012-07-23 09:27:00.000	2012-07-23 09:27:30.607
2012-07-23 07:30:00.000	2012-07-23 09:27:00.000	2012-07-23 09:27:31.010
2012-07-23 07:30:00.000	2012-07-23 09:27:00.000	2012-07-23 09:27:31.417
2012-07-23 07:30:00.000	2012-07-23 09:32:00.000	2012-07-23 11:09:10.627

do you thinki have to use datediff with a less precise datepart.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 12:53:17

it depends on logic you're using inside datediff. what date intervals were you passing to datediff? also whats was datatype you used for variable to store the datediff returned value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thinkingeye
Starting Member

7 Posts

Posted - 2012-07-23 : 14:00:48

set @duration = datediff(ss,@start_datetime, @end_datetime)

and the reutn dataype is an Integer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 15:08:04

nope..i'm sure somewhere you're having bad data. the above shown data will not cause an error if used with expression above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources