Data type conversion - SQL Server Forums

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)

Data type conversion

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-16 : 07:42:04

Marco writes "LS,

I created a DTS.

In the first step I import an ASCII-file into a table with only varchar (255) fields.

In the second step I want to update another table with the data from this first table. In the second table there are a few varchar (255) fields and real fields.

I just use the update statement, but I receive the following error: "Error converting data type varchar to real".

Do I have to use convert, and if so, how can I use it?

Thanks in advance,
Marco."

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-16 : 08:11:59

Try:

UPDATE MyRealTable
SET RealColmn = CAST(MyVarcharcolumn as FLOAT(7))
FROM MyRealTable A
INNER JOIN MyFloatTable B
ON A.Key = B.Key

Most of the examples I see in this newsgroup use CONVERT not CAST. I think it's a personal preference. I can remember CAST syntax easily.

Sam

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2003-04-16 : 08:38:46

I think CAST was introduced from SQL7 upwards. Prior to that you had to use CONVERT

===========
Paul

dsdeming

479 Posts

Posted - 2003-04-16 : 09:36:54

If I'm not mistaken, CAST is ANSI standard and CONVERT is not. I personally use CAST for all conversions except those involving dates. CONVERT has a style argument which allows you to convert dates into a variety of formats.

Sulemank
Starting Member

1 Post

Posted - 2003-04-16 : 10:25:33

I've got a problem which comes under the same topic, hope u dont mind me asking it here....!

I've got 2 different scenarios:

Scenario 1:

select * from creditor
WHERE convert(nvarchar(10),updated,21) = '01/04/2003'

Scenario 2:

SELECT * from creditor
WHERE Updated >= '01/04/2003 00:00:00' AND
Updated <= '02/04/2003 00:00:00'

Note: The 'Updated' column is of SQL type "DATETIME" as defined in the table design for Creditor. Both return the same data.

Questions:

1. Which of the 2 scenarios will perform the quickest??

Scenario 1 implicitly seems to convert the date value "01/04/2003 00:00:00" provided to a DATETIME.

Scenario 2 explicitly converts the updated field to a NVARCHAR type (10 in length).

The following will help answer Question 1:

2. For Scenario 1, does SQL Server convert 'UPDATED' for each row processed in table CREDITOR?

3. For Scenario 2, does SQL Server do an implicit conversion of '01/04/2003 00:00:00' the ONCE or does it convert this value for each row returned from table CREDITOR?

Note: Have already used the Trace in SQL Query Analyser... and I'm getting inconsistent CPU and DURATION times when executing each of these querys after clearing the CACHE before running each one."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources