Convert Varchar(220) to INT or DECIMAL Help

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

Transact-SQL (2008)

Convert Varchar(220) to INT or DECIMAL Help

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-07-03 : 10:33:52

My code worked last week but now all of the sudden things stopped working and I need help. I keep getting this Conversion Failed when converting the varchar value '488.8 to data type int.

SELECT [sa-hist].[cust-no],

CASE WHEN CAST((sa.[sa-fields__1]) AS INT) = ''
THEN '0'
ELSE CAST((sa.[sa-fields__1]) AS INT) END AS Fuel

,CASE WHEN CAST((sa.[sa-fields__2]) AS INT) = ''
THEN '0'
ELSE CAST((sa.[sa-fields__2]) AS INT) END AS LPFuel

FROM
sa AS sa INNER JOIN
[sa-hist] AS [sa-hist] ON sa.[sa-no] = [sa-hist].[sa-no]

WHERE ([sa-hist].[sa-type] LIKE 'F%')

I know the fields (Fuel and LPFuel) holds other values like dates and numbers and the Varchar is (220)

Cust-no,Fuel, LPFuel
010336 891.8 0
010526 428.5 0
011036 658.5 0
011335 509.5 0
011343 789.5 44.6
011531 1171 0
011631 862.7 0
012640 557.5 0
012985 944.6 0
013018 728.7 0
013080 426.2 0
013101 565.6 0
013237 1038.3 0
014446 556.8 0
014605 1064.1 0
014767 395 167.9
014904 215.6 0
015051 612.1 0
015228 1288.8 0
016775 469.6 0
017902 328.5 0
019073 591.8 0
020460 594.6 0
020796 725.9 0
020861 475.7 0
022235 668.7 0
022349 462.5 0
022437 892.2 0
022581 871.7 0
022583 445.2 0
022598 846.7 0
022607 448.4 0
022724 876 0
023143 457.4 0
024307 734.1 0
024312 382.5 0
024471 760.9 0
024569 468.5 0
024901 1350.1 0
025573 410.3 0
025586 433.4 0
025670 693.7 0
025742 788.6 0
026766 494.8 0
027287 505 0
027440 592.5 0
027800 636.3 0
027819 946.8 0
028062 417 0
028113 731.8 0
028313 701.3 0
028349 497.1 0
030012 678.7 0
030195 619.2 0
030251 621 0
030838 750.2 0
030891 316.4 0
030907 445.8 0
031545 300.6 0
031659 279.4 0
031661 457.3 0
032092 404.6 0
032443 311.6 0
032588 621.4 0
032676 607 0
032744 696.5 0
032780 340.7 0
032996 237.8 0
033356 391.5 0
033566 1189.4 0
034173 839.2 0
034195 1487.7 0
034338 675.7 0
034512 531.3 0
034753 510.8 0
035390 940 0
035394 417.7 0
035605 846 0
035732 352 0
036192 515.9 0
036240 746.7 0
036669 610 0
037437 926.9 0
038828 919.7 0
040346 883 0
040797 488.5 0
040865 779.7 0
041100 192.7 0
041613 500 0
041972 968.9 0
042000 697.6 0
042266 911.7 0
042639 539 0
042855 869.9 0
043234 841.1 0
043242 189 0
043537 750.6 0
043895 428.9 0
044256 492.9 0
044657 611.2 0
044678 1243.5 0
044716 383.5 0
044763 625.2 0
045271 846 0
045317 909.4 0
045673 415.9 0
045677 626.8 0
045778 867.8 0
046042 633.3 0
046300 1082.7 0
046314 687.2 0
046325 1126.8 0
046365 850.8 378.7
046378 747.4 0
046382 800 0
046461 693.3 0
046470 423.6 0
046623 405.8 0
046639 552.2 0
046641 637.8 0
046643 706.4 0
049416 580.3 0
052124 583.5 0
052798 418.9 0
057069 435.7 0
058343 377.7 0
058805 415.3 0
058816 896.4 0
063060 743.9 0
063061 529.8 0
063147 532.2 0
063180 826.1 0
063282 733.8 0
063339 1016.4 0
063341 587.5 0
063414 617.9 0
063519 447.4 0
063572 849.6 0
063621 660.2 0
075059 520.9 0
075417 648.1 0
075499 863.3 0
100803 699.5 0
101872 570.3 0
102226 1571 0
102434 477.1 0
103824 571.6 0
104480 328.9 0
104670 699.8 0
104829 728.1 0
200638 366.7 0
201053 594.4 0
201206 1009.4 0
201362 733.2 0
201480 734.6 0
202209 410.2 0
202934 605.5 0
204325 619.3 0
204474 628.8 0
204877 521.2 0
205208 728.2 0
300404 724.9 0
301204 457.1 0
301751 629.4 0
302116 873.7 0
302245 402.4 0
302303 638.1 0
303038 872 0
304139 643.8 0
304437 573 0
305866 926.5 0
400278 673 0
400506 1106.7 0
400867 169.1 0
402404 848.6 0
402445 386.7 0
402506 610.5 0
403403 727.9 0
403456 694.7 0
035021 214.6 0
036368 297.1 0
049154 425.7 0
S10434 913.3 0
S10308 656.7 0
S10747 154.9 0
100257 250 0
H12361 750 0
063098 514.3 0
S10364 465.9 0
H12450 450 0
063435 683 0
031194 550 0
S10844 759.6 0
034425 567.9 0
H11679 178.2 0
010534 384 0
063213 349.6 0
063402 532.60 0
043109 702.2 0
063389 702.2 0

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-03 : 10:55:52

Do something like this:

SELECT CAST(CAST('488.8' AS FLOAT) AS INT);

Also, if you must, change those case expressions like in the example below, but it is not really required, you can just use the double-casting as I showed above without the case expression:

CASE WHEN  sa.[sa-fields__1] = '' THEN 0
     ELSE CAST(CAST(( sa.[sa-fields__1] ) AS FLOAT) AS INT)
END AS Fuel ,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources