Converting data types in SQL Server

Question

I need to change some data types. I have tried using the CAST keyword, but it is not fixing the issue. Any suggestions on how to re-write these two lines with CAST statements?

Data loss might occur when casting from VarChar(100) to VarChar(20).

Code:

SELECT TOP 1 @variableId = variableId
FROM @tempTable

Data loss might occur when casting from NVarChar(512) to NVarChar.

Code:

SELECT myVariable
FROM tableName

I tried doing something like the following, but still produces an error:

CAST((myVariable) as nvarchar)

What is your problem? When you shorten a character string, you might lose data. That is expected and why you get the warning. — Gordon Linoff
– Gordon Linoff, Commented Jun 16, 2014 at 19:14
Please be more specific. What did you try? How does your data and schema looks like? The messages you provided are quite self-explaining: Your target type is to small to keep data from the source data type. — Martin K.
– Martin K., Commented Jun 16, 2014 at 19:14

shA.t · Accepted Answer · 2015-05-04 06:14:37Z

2

CAST is used for converting between data types. It sounds more like you should use LEFT instead:

SELECT TOP 1 @variableId = LEFT(variableId,20)
FROM @tempTable

This won't give you any warning as the system assumes you already know you're going to lose the right 80 characters.

edited May 4, 2015 at 6:14

shA.t

17k5 gold badges59 silver badges121 bronze badges

answered Jun 16, 2014 at 19:17

Dave Jemison

7043 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shA.t · Accepted Answer · 2015-05-04 06:13:35Z

0

For using CONVERT instead of CAST:

CONVERT(nvarchar, myVariable)

Is a valid syntax and also:

CONVERT(varchar(20), myVariable)

But for getting a part of a string we use SUBSTRING() or LEFT() or RIGHT() functions like this:

SUBSTRING( value_expression , start_expression , length_expression )

Returns part of a character, binary, text, or image expression.
LEFT ( character_expression , integer_expression )

Returns the left part of a character string with the specified number of characters.
RIGHT ( character_expression , integer_expression )

Returns the right part of a character string with the specified number of characters.

answered May 4, 2015 at 6:13

shA.t

17k5 gold badges59 silver badges121 bronze badges

Collectives™ on Stack Overflow

Converting data types in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related