Johnny Bones Johnny Bones - 6 months ago 9
SQL Question

Rounding a field's value in a SELECT statement

What I need to do is to round a field to 2 decimals, but not in the usual way. I have a dropdown that's always rounded to 2 decimals (CIT_NBR). However, in the database table, it's sometimes rounded to 1 decimal. So now I'm trying to create a SELECT statement based on this field, but my front end stores it as 2 decimals and my back end can be stored as either 1 or 2 decimals. Don't ask, it's complicated. :o)

So, what I want to do in "aircode" is something like:

SELECT * FROM VW_MOS_DPL_AccountValidation WHERE CUST_NUM = @CNum
AND Format(CIT_NBR, 2 decimals) = @CITNum


This way, it forces the data in the table to use 2 decimals, so it can be compared to my dropdown.

Here's my code block:

using (SqlConnection con2 = new SqlConnection(str2))
{
using (SqlCommand cmd2 = new SqlCommand(@"SELECT * FROM VW_MOS_DPL_AccountValidation WHERE CUST_NUM = @CNum AND CIT_NBR = @CITNum", con2))
{
con2.Open();

cmd2.Parameters.AddWithValue("@CNum", TBAccountNum.Text);

string ddlCITVal2 = ddlCIT.SelectedValue;
cmd2.Parameters.AddWithValue("@CITNum", ddlCITVal2);

using (SqlDataReader DT2 = cmd2.ExecuteReader())
{
// If the SQL returns any records, process the info
if (DT2.HasRows)
{
while (DT2.Read())
{
.
.
.
etc


How could I go about doing this?

Answer

Cast the varchar to a decimal

SELECT SUM(Cast(CitNum as decimal(8,2))) as CitNum FROM table