Hello,

if I create calculated field in a table using formula e.g., ColumnName/ 60, where format of ColumnName is INT (total ammount of minutes)  in order to get hours, I get only integer part, not decimal. E.g., 80 (minutes)/60 =1.3333 but, in calculated field I see only 1 hrs without possibility to change format of the field.

Can calculated field be used on this way? (My wish  is to get 1.3 in that field)

How?

Thanks

Division of 2 integers will be truncated down to an integer.  So 80/60 which is 1.3333... will become 1.  To avoid this, one of the values must not be an integer either because you cast it to something else or by making one of the values not an integer.  So either Cast(ColumnName As decimal(12,0))/60 or ColumnName/60.  (note the period making 60. a decimal value, whereas 60 is an integer value)  will work to make the result a decimal.

Since you want 1.3 and not 1.333333, you must then cast to a decimal with one digit to the right of the decimal point.  So you would want something like

Cast(ColumnName/60. As decimal(12,1))

Tom

• Proposed as answer by Russ Loski 16 hours 53 minutes ago
• Marked as answer by daredavil3011 15 hours 44 minutes ago

Of course the calculated field/column can not be an INT, if it is then change that.

Does something like this work?
CAST(ColumnName/60 AS Decimal(10,2))

• Marked as answer by daredavil3011 15 hours 41 minutes ago

Hi,

it works...I have created formula as you proposed, the only what SQL Server 2008 R2 did, automatically, is this:

(CONVERT([decimal](12,1),[OUT_IN_1]/(60.),0)). Namely, SQLServer automatically, by itself, changed format of formula as shown above.

A propos this, in WEB app, I had to format display property into

###.0

format.

That is all, thans indeed for prompt and correct advice....

hi,

thanks to you, too....I was already in process of implementation of the proposal of Mr. Tom. As much I can see, your proposal is the same....

Thanks

what SQL Server 2008 R2 did, automatically, is this:

(CONVERT([decimal](12,1),[OUT_IN_1]/(60.),0)). Namely, SQLServer automatically, by itself, changed format of formula as shown above.

That's fine,

(CONVERT([decimal](12,1),[OUT_IN_1]/(60.),0))

does exactly the same thing as

Cast([OUT_IN_1]/60. As decimal(12,1))

Tom

BTW, if your are going to be writing a lot of SQL, particularly complex statements, I would recommend you write your own SQL and not use query generators or designers (which I presume is what transformed the above expression).  Every query designer I have have seen, including Microsoft's have some things they cannot do and/or some things they do very inefficiently and/or some things they do wrong.

If you are only going to occasionally do straight forward queries, query designers are probably fine.  Learning all of SQL is fun, but probably not worth it if you will only use it occasionally.

But there is no problem in the present case.  The two expressions do exactly the same thing and are equally efficient.  I prefer mine because I think it is simpler and easier to read and certainly less typing, but the other is perfectly fine.

Tom