Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Convert Varchar(220) to INT or DECIMAL Help
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

120 Posts

Posted - 07/03/2013 :  10:33:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 07/03/2013 :  10:55:52  Show Profile  Reply with Quote
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 ,
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000