52

I want to create a view in which I select something like the following:

select id, name, 1 as active
from users

However, I want the active field, which I am creating in the select statement (it doesn't exist in the table), to be a bit field. Is there a way to do this?

dmr
  • 21,811
  • 37
  • 100
  • 138

4 Answers4

85

You can use the CONVERT operator.

SELECT id, name, CONVERT(bit, 1) AS active
FROM users

CAST or CONVERT will work.

bobs
  • 21,844
  • 12
  • 67
  • 78
15
select id, name, CAST(1 AS bit) as active
from users

1 is the display for a true bit. What are your trying to achieve.

Doing 

select CAST('true' AS bit) as active

returns 1 also.

Dustin Laine
  • 37,935
  • 10
  • 86
  • 125
6

Yes, you cast it to bit:

select id, name, cast(1 as bit) as active
from users

This can also be useful to improve performance when comparing to a bit value in some situations:

select id, name
from users
where active = cast(1 as bit)

(In this example it might make no practical difference, but I have seen an actual difference in more complicated queries.)

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
3
select id, name, Convert(bit, 1) as active
from users

Is what you probably are wanting to do.

msarchet
  • 15,104
  • 2
  • 43
  • 66