SQL Server - Boolean Literal

SQL Server - boolean literal?

SQL Server doesn't have a boolean data type. As @Mikael has indicated, the closest approximation is the bit. But that is a numeric type, not a boolean type. In addition, it only supports 2 values - 0 or 1 (and one non-value, NULL).

SQL (standard SQL, as well as T-SQL dialect) describes a Three valued logic. The boolean type for SQL should support 3 values - TRUE, FALSE and UNKNOWN (and also, the non-value NULL). So bit isn't actually a good match here.

Given that SQL Server has no support for the data type, we should not expect to be able to write literals of that "type".

sql server: boolean literals

Yep, typically 1=1 or 1=0. There is no other way in T-SQL.

Is there a Boolean data type in Microsoft SQL Server like there is in MySQL?

You could use the BIT datatype to represent boolean data. A BIT field's value is either 1, 0, or null.

SQL Server boolean operators

SQL Server does have a Boolean data type. You can open the Logical Operators (Transact-SQL) manual page and find the following statement:

Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.

It's just that you can't use this type in the same way you can use other Transact-SQL data types. For instance, you can't declare boolean variables or arguments, add boolean columns to tables, cast to/from a boolean. But you can have boolean expressions and use them in contexts where they are required (WHERE, ON, check constraints…). You can also apply boolean operators to those expressions: AND, NOT et al. (Operators like <, =, LIKE and other can also be considered boolean, in the sense that they return boolean results, but their operands are actually never booleans.)

So, to summarise, there is a boolean type in SQL Server but its use is limited, as described above. Why? My answer may be a silly one, sorry, but that's one that I'm satisfied with: this is the way they chose it to be.

Is there a way to get a boolean without casting in SQL Server?

Sql Server doesn't have a boolean datatype you can store in a table, but it does contain a bit data type you can store.

However, the true keyword you see IS a boolean value. Sql Server only uses the boolean data type for results for its Comparison Operators, which can return three values, TRUE, FALSE, and UNKNOWN.

It is important to know the distinction between the two.

SELECT *
FROM Table
WHERE Field = 1 --Field = true

To store boolean values in a table, use the bit datatype. Use 1 for true, 0 for false, and Null for unknown.

Checking for entered date in SQL

Your statement is invalid in sql server. You can have WHERE c.Closes IS NOT NULL or you can use ISNULL command to replace the null values with something else.

How can I select a boolean literal in HQL?

There are actually no real booleans in SQL (as in Select 1=1), except of in conditions. HQL turns "true" and "false" into 0 and 1 I think (something you can configure somewhere), but I guess it is still an integer.

Simplest things you can most probably do is convert the integer to a boolean in memory after the query.

Reverse a boolean based on a SQL CASE WHEN or IIF

This started out as a comment but soon got to big, so...

To expand on scaisEdge's and Sami's answers:

IIF is syntactic sugar for simple case expressions, with only one then and an else part. The remarks section of the documentation starts with this:

IIF is a shorthand way for writing a CASE expression. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation.

The case expression returns a single scalar value based on which condition is met.

It's documentation clearly states (again, in the remarks section), that it can't be used to control the flow of the T-SQL script:

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

Therefor, the problem in your code is this part Completed = 'False', inside the case statement you can only write code that will return a single scalar value, but this code does not do that.

SQL JSON - How to modify boolean value present in the Json Data

You can accomplish this using CAST(0 as BIT)

update TestTable
set jsonData = JSON_MODIFY(jsonData, '$.isActive', CAST(0 as BIT))

If you want to set it to true, it is simply CAST(1 as BIT) instead.

This works because in SQL, a boolean is represented as a BIT, which can only be 0 or 1. In its translation to JSON, it converts a bit of 0 to false, and a bit of 1 to true.