SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 conversion of char data type to smalldatetime data type erro
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/30/2006 :  07:08:16  Show Profile  Visit AskSQLTeam's Homepage
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

Sweden
29910 Posts

Posted - 10/30/2006 :  07:23:06  Show Profile  Visit SwePeso's Homepage
Already asked and answered here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74073


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000