|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 10/30/2006 : 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 INT
DECLARE @msg VARCHAR(100)
--Extract the file date from the file
SELECT
@fileDate = File_Date
FROM
Temp_Sales_Data
WHERE
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_ID
SELECT @timeID = Time_ID FROM [Time] WHERE [Date] = convert(varchar(10), convert(smalldatetime, @fileDate), 20)
--Insert the Time_ID
UPDATE Temp_Sales_Data
SET Time_ID = @timeID
--Delete the header and footer records from the file
DELETE FROM Temp_Sales_Data
WHERE RTRIM(RR_ID) IN ('SALES ANALYSIS FOR', 'END')
--Check that the Time_ID has been assigned
SELECT * FROM Temp_Sales_Data WHERE Time_ID IS NULL
IF @@ROWCOUNT > 0
BEGIN
SELECT @msg = 'Time_ID not set correctly. Check that there is a suitable entry in the Time table'
RAISERROR(@msg, 16, 1)
END
The 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 ?" |
|
conversion of char data type to smalldatetime data type erro
Forum Locked
