I would like to select boolean value:
SELECT field1, field2, 1 as is_field
FROM TABLE
In Visual Studio 2010 I am doing:
bool b = row.Field<bool>("is_field");
But I get the exception:
Specified cast is not valid.
How can I return a boolean value as boolean
and not as int
?
Details of sql differ. SQL Server does not support a Boolean type e.g. SELECT WHEN CAST(1 AS BIT) THEN 'YES' END AS result -- results in an error i.e. CAST(1 AS BIT) is not the same logical TRUE.
Boolean comparisons are used to test the operands and to return boolean values. Boolean expressions can belong to any other types of 4gl expressions. For example, you can use INT and SMALLINT to store the value returned by a Boolean expression.
In SQL Server, a Boolean Datatype can be created by means of keeping BIT datatype. Though it is a numeric datatype, it can accept either 0 or 1 or NULL values only. Hence easily we can assign FALSE values to 0 and TRUE values to 1.
CREATE OR REPLACE FUNCTION fun_tiene_cita (id_paciente number, fecha_cita date) return number is begin if (exists(select id_paciente from citas where id_paciente = 500 and fecha_cita = 03/03/2020)) then return 'true'; else return 'false'; end if; END fun_tiene_cita; Obviously, it isn't working, so, what could I do?
From SQL side, you can do:
SELECT field1, field2, CAST(1 AS BIT) AS is_field
FROM TABLE
to force it to be returned as a BIT instead of an int. That should do the trick
Update: What is your concern for using CAST like this? SQL Server is pretty awesome at optimising e.g. the execution plan for above query would show is_field is a Constant which can be evaluated once up front (there's a related article here: http://msdn.microsoft.com/en-us/library/ms175933.aspx). Even without that, if you're concerned about performance, then you shouldn't worry about it - it would very much be premature optimisation.
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