Or, because the second operator in this example is a literal, you could merely add a decimal point after the second operator: SELECT package_weight / 16. Note that the period after the 1 is required to define a number as a decimal.
Perhaps you've seen some mystery code that looks like this: SELECT (package_weight * 1.) / 16 * $2.00 FROM. Or SELECT CAST(package_weight AS decimal(6,2)) / 16 * $2.00 FROMĪlternatively, you can use a shorthand way to get the correct answer to the problem. If you can't or don't want to change the package_weight column's data type, you can use the CAST() or CONVERT() function to convert the column to a decimal: SELECT CONVERT(decimal(6,2),package_weight) / 16 * $2.00 FROM packages Now you get the answer you need to stay in business: Bob owes $1 for his 8-ounce package. You can take advantage of this precedence in your delivery problem by changing to a decimal: DECLARE decimal(6,2) For example, dividing an integer by a decimal results in a decimal because decimal is higher than integer in precedence. Figure 1 shows the precedence order for SQL Server 2000 data types. If the two operands have differing data types, SQL Server converts the intermediate and final result to the higher precedence data type. Using integer math, 8 divided by 16 equals 0 (with a remainder of 8), and 0 multiplied by $2 is still 0. In this case, both operands (8 and 16) are integers. The problem here is that SQL Server keeps intermediate results in the same data type as the operands in an equation. At 16 ounces, Bob will owe your company $2 at less than 16 ounces, he'll owe nothing. Now, increase the package weight to 16 ounces. This code's result shows that Bob gets his package delivered for free (.0000). SELECT = 8 -Bob's package weighed 8 ounces To calculate how much to charge a customer named Bob for delivering his 8-ounce package, you might use a SELECT statement like this: SELECT package_weight / 16 * $2.00īob should owe $1, right? You can use the following script to run a quick check of this answer without even creating a table: DECLARE int You store the package weight in ounces as an integer in your database. Let's say your company charges $2 per pound to deliver small packages. Related: Mapping T-SQL Data Types to SqlTypes Let's look at some of these behaviors and how you can keep them under control. Some SQL Server data types-including integer, character, numeric, datetime, and uniqueidentifier-also behave in ways that might surprise you. Have you ever walked toward a door, pushed hard to open it, and- pow!-slammed right into it? The door opened the other direction, of course. Sometimes things just don't work the way you expect.
0 kommentar(er)
