How to display sql server boolean type True & False for 1 & NULL ?

In SQL Server, the BIT data type is used to represent boolean values. A BIT can have a value of 0, 1, or NULL. To display TRUE and FALSE for 1 and NULL respectively, you can use the CASE statement to convert these values in your query.

Here’s an example query demonstrating how to do this:

Example Table:

Assume you have a table named ExampleTable with a column named IsActive of type BIT.

  CREATE TABLE ExampleTable (    ID INT PRIMARY KEY,    IsActive BIT);

  INSERT INTO ExampleTable (ID, IsActive) VALUES (1, 1), (2, NULL), (3, 0);

Query to Display TRUE and FALSE:

You can use the CASE statement to convert the BIT values to TRUE or FALSE.

  SELECT     ID,    CASE         WHEN IsActive = 1 THEN 'TRUE'        WHEN IsActive = 0 THEN 'FALSE'        ELSE 'FALSE'  -- Defaulting NULL to 'FALSE'    END AS IsActiveStatusFROM     ExampleTable;In this query:

If IsActive is 1, it displays TRUE.

If IsActive is 0 or NULL, it displays FALSE.

If you specifically want to distinguish between NULL and 0, you can modify the CASE statement accordingly:

  SELECT     ID,    CASE         WHEN IsActive = 1 THEN 'TRUE'        WHEN IsActive = 0 THEN 'FALSE'        WHEN IsActive IS NULL THEN 'FALSE'  -- Explicitly handling NULL    END AS IsActiveStatusFROM     ExampleTable;

In this version, you explicitly check for NULL values to ensure they are displayed as FALSE.

Displaying Custom Text:

If you need more customized text for TRUE and FALSE, simply replace 'TRUE' and 'FALSE' with your desired strings.

  SELECT     ID,    CASE         WHEN IsActive = 1 THEN 'Active'        WHEN IsActive = 0 THEN 'Inactive'        ELSE 'Inactive'  -- Defaulting NULL to 'Inactive'    END AS IsActiveStatusFROM     ExampleTable;

This approach ensures that your query result displays human-readable boolean values based on the BIT column in your SQL Server table.