CODEWITHSUNDEEP
sqlsql-servert-sql
AffenkingLouie
AffenkingLouie

Reputation: 21

CAST always returns 1

I'm executing this TSQL Code:

DECLARE @myString varchar;
SET @myString = '123.0'
SELECT CAST(@myString as decimal(25,10))

But I keep getting 1.00000 as an result Changing myString to '123' doesn't change that.

Any advise on what I'm doing wrong is appreciated.

Thanks in advance!

Upvotes: 1

Views: 97

Answers (2)

huMpty duMpty
huMpty duMpty

Reputation: 14460

You are missing the varchar declareation 

DECLARE @myString varchar(10);
SET @myString = '123.0'
SELECT CAST(@myString as decimal(25,10))

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269445

ALWAYS use length when using varchar() (and related types) in MySQL. The default is 1 in this context. So this fixes your problem:

DECLARE @myString varchar(255);
SET @myString = '123.0';
SELECT CAST(@myString as decimal(25,10));

You are getting 1, because your code is interpreted as

DECLARE @myString varchar(1);
SET @myString = '123.0';
SELECT CAST(@myString as decimal(25,10));

The documentation is not shy about this:

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

Upvotes: 7

Related Questions