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
 Old Forums
 CLOSED - General SQL Server
 conversion of char data type to smalldatetime data type erro

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 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 ?"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 07:23:06
Already asked and answered here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74073


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -