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.
Author |
Topic |
AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2006-10-30 : 07:08:16
|
Jaspreet writes "Hi All,In MS-SQL server, I am executing a SQL task in a DTS package. This SQL task runs perfectly on one server but the same code fails on the other one prompting with this error message :"conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value".The Code in the SQL Task is :DECLARE @fileDate VARCHAR(10)DECLARE @timeID INTDECLARE @msg VARCHAR(100)--Extract the file date from the fileSELECT @fileDate = File_DateFROM Temp_Sales_DataWHERE RTRIM(RR_ID) = 'SALES ANALYSIS FOR' --format the value SET @fileDate = SUBSTRING(@fileDate, 5, 4) + '/' + SUBSTRING(@fileDate, 3, 2) + '/' + SUBSTRING(@fileDate, 1,2)--Select the approp. Time_IDSELECT @timeID = Time_ID FROM [Time] WHERE [Date] = convert(varchar(10), convert(smalldatetime, @fileDate), 20)--Insert the Time_IDUPDATE Temp_Sales_DataSET Time_ID = @timeID--Delete the header and footer records from the fileDELETE FROM Temp_Sales_DataWHERE RTRIM(RR_ID) IN ('SALES ANALYSIS FOR', 'END')--Check that the Time_ID has been assignedSELECT * FROM Temp_Sales_Data WHERE Time_ID IS NULLIF @@ROWCOUNT > 0BEGIN SELECT @msg = 'Time_ID not set correctly. Check that there is a suitable entry in the Time table' RAISERROR(@msg, 16, 1)ENDThe statement that creates the problem is :SELECT @timeID = Time_ID FROM [Time] WHERE [Date] = convert(varchar(10), convert(smalldatetime, @fileDate), 20)Does any one have a clue why this statment works perfectly on one server while fails on the other one ?" |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|