Menu
I have two bit type columns in my sql table, and the values in it are like,
FirstHalfLeave SecondHalfLeave
------------ -------------
0 1
1 1
I need to sum these two fields to make it leave for a single day and I need to dislay the exact result(3/2=1.5)
I just converted these bit to integer like
sum(CAST(StaffAttendance.FirstHalfStatus as Integer) +
CAST(StaffAttendance.SecondHalfStatus as integer))/2 as TotalLeave
it showing the result as 1 not 1.5, for this I think I need to cast it to double or to float, I dunno how to do this, can anyone help me here, thanks in advance
727
In this case the order of precedence is in your favour, and you get a float on both sides, and a float as a result of the + . But SUM(aFloatField) + 0 does not yield an INT, because the 0 is being implicitly cast to a FLOAT. I find that in most programming cases, it is much preferable to be explicit.
Another function to convert implicitly when we concatenate values is the CONCAT function. This example shows how to concatenate string values with int values. The CONCAT implicitly converts values into strings. If you need to concatenate different data types, CONCAT is the best option instead of using the + character.
The cast and convert functions provide similar functionality. They are used to convert a value from one data type to another. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
Use the CAST() function to convert an integer to a DECIMAL data type. This function takes an expression or a column name as the argument, followed by the keyword AS and the new data type. In our example, we converted an integer (12) to a decimal value (12.00).
The easiest way to convert to double is multiplying by 1.0 like this:
1.0 * (sum(CAST(StaffAttendance.FirstHalfStatus as Integer) +
CAST(StaffAttendance.SecondHalfStatus as integer)))/2 as TotalLeave
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
