Cast/Conversions - 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

General SQL Server Forums

New to SQL Server Programming

Cast/Conversions

Author

Topic

aerosmith
Starting Member

9 Posts

Posted - 2014-07-10 : 19:58:46

Hello,

using sql 2008, trying to convert a string to int

Example
Table1 has a varchar which i want to cast to int
00125000000 to int 125000000
i used the following
cast(B.[Assessment] as int) as [USER1_01],
with the result 125000000

Table2 has the same field in a different format.
0.00125

i need to find a way to convert one of them so they both match. if i did a join and matched the fields.

I tried
cast (10000000000 * cast([USER1_01] as NUMERIC(6,6)) as INT) as [USER1_01]

but for some reason it dosent look like they match, although they look the same. maybe a type problem?.

I hope someone can shed some help me, been working on this for a long time :(

aerosmith
Starting Member

9 Posts

Posted - 2014-07-10 : 20:15:05

i read online about double conversion but that is just confusing to me,
example
select CONVERT(int,CONVERT(decimal(19,2),'12.80'))

not sure where the 19,2 comes from, i assume the 2 is two decimal places?

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2014-07-11 : 02:56:53

Use Brackets properly for expected results in expressions
select cast( (100000000000 * (cast(0.00125 as NUMERIC(6,6))) ) as INT) as [USER1_01]

Right now you are multiplying with 10000000000 to get match with first expression result multiply with 100000000000 (One additional zero)

M.MURALI kRISHNA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-11 : 03:36:24

SELECT CAST(100000000000E * 0.00125 AS INT) AS [USER1_01]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources