CODEWITHSUNDEEP
t-sql
Lisa
Lisa

Reputation: 3181

How to generate only o or 1 for a bit column?

I need to fill a bit column only with 0 or 1, I tried the following, which only gave me 1:

convert(bit, 2*rand())

How can I make it work?

Upvotes: 3

Views: 5795

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

If you need multiple rows, and you don't care about the specific distribution of values, something like the following may be appropriate:

CONVERT(bit,SUBSTRING(CONVERT(binary(18),newid()),1,1)&1)

This will be evaluated once per row

Upvotes: 6

Martin Smith
Martin Smith

Reputation: 453358

If you are on SQL Server 2008 you can use

CAST(CAST(CRYPT_GEN_RANDOM(1) AS int)%2 AS BIT)

For previous versions you can use

CAST(CAST(CHECKSUM(NEWID()) AS int)%2 AS BIT)

Upvotes: 2

Adam Robinson
Adam Robinson

Reputation: 185643

Passing in any value that's greater than 0 to this expression will yield a 1 every time. In order to do what you want, you need to round the results yourself:

select convert(bit, round(1*rand(),0))

This will give you a random 0 or 1 bit value.

Upvotes: 5

Related Questions