conversion of char data type to smalldatetime data

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 2000 Forums

SQL Server Development (2000)

conversion of char data type to smalldatetime data

Author

Topic

jasraja
Starting Member

22 Posts

Posted - 2006-10-26 : 06:26:29

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 ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-26 : 06:34:35

duplicate post:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74074[/url]

Harsh Athalye
India.
"Nothing is Impossible"

jasraja
Starting Member

22 Posts

Posted - 2006-10-26 : 06:52:28

Its duplicate but still worth it.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-26 : 07:21:45

quote:
Originally posted by jasraja

Its duplicate but still worth it.

What does that supposed to mean? It's of no use to flash same question twice in different forums (unless you want to annoy people).

Harsh Athalye
India.
"Nothing is Impossible"

jasraja
Starting Member

22 Posts

Posted - 2006-10-26 : 07:39:07

Well, I dont intend to annoy anyone. I posted in two different forums for the two reasons :

1. B'coz I think, It was relevant for both of them.
2. I want a qucik solution to my problem (I know I am mean here)

No Personal Offence.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-26 : 08:31:18

"B'coz I think, It was relevant for both of them."
Post at the most relevant forum will do.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-26 : 08:40:03

quote:
Originally posted by jasraja

Well, I dont intend to annoy anyone. I posted in two different forums for the two reasons :

1. B'coz I think, It was relevant for both of them.
2. I want a qucik solution to my problem (I know I am mean here)

No Personal Offence.

Cross posting ususlly results in making people mad, and you not getting any help at all.

CODO ERGO SUM

jasraja
Starting Member

22 Posts

Posted - 2006-10-26 : 09:15:09

I am sorry, if it really made u mad.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources