Convert "TinyInt" into Binary format and store the multiple bit values in columns

Question

Post reply

Convert "TinyInt" into Binary format and store the multiple bit values in columns

kish1234

Ten Centuries

Points: 1112
More actions
April 2, 2014 at 6:27 pm

#287187

Hi team,
I have an requirement which I need expertise help on how to write the logic.
Our system records the appointment details in the back end ( Pattern Column) in "tinyint" format.
For eg - if the number in that column is 55, I need to write a view with the below columns and they need to populated with the converted binary values as below. As this indicate, the client have appointment on the day where the value is "1".
SatFriThuWedTueMonSun
0110111
Thanks for the help in advance.
Regards,
Krishna.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Do you need something like this?

WITH SampleData AS(

SELECT 55 Value UNION ALL

SELECT 63 UNION ALL

SELECT 26

)

SELECT Value,

(Value & 64) / 64AS Sat,

(Value & 32) / 32AS Fri,

(Value & 16) / 16AS Thu,

(Value & 8) / 8AS Wed,

(Value & 4) / 4AS Tue,

(Value & 2) / 2AS Mon,

(Value & 1) / 1AS Sun

FROM SampleData

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Just a small simplification of Luis' fine code...

WITH SampleData AS(

SELECT 55 Value UNION ALL

SELECT 63 UNION ALL

SELECT 26

)

SELECT Value,

SIGN(Value & 64) AS Sat,

SIGN(Value & 32) AS Fri,

SIGN(Value & 16) AS Thu,

SIGN(Value & 8) AS Wed,

SIGN(Value & 4) AS Tue,

SIGN(Value & 2) AS Mon,

SIGN(Value & 1) AS Sun

FROM SampleData

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

And if you need them as bit values:

WITH SampleData AS(

SELECT 55 Value UNION ALL

SELECT 63 UNION ALL

SELECT 26

)

SELECT Value,

CAST(Value & 64 AS bit) AS Sat,

CAST(Value & 32 AS bit) AS Fri,

CAST(Value & 16 AS bit) AS Thu,

CAST(Value & 8 AS bit) AS Wed,

CAST(Value & 4 AS bit) AS Tue,

CAST(Value & 2 AS bit) AS Mon,

CAST(Value & 1 AS bit) AS Sun

FROM SampleData

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kish1234 Ten Centuries Points: 1112 More actions · Answer 4

Hi Team,

Thanks all for your replies. On the same topic, I did the following approach and it is throwing an error, if I include the statement which I have highlightes in italic and bold.

If I dont include that part of the statement, it is working fine And I include that the following are the error message

" 'iif' is not built in recognised fucntion"

" Incorrect syntax near "recurrence" , expecting conversation"

and all the select columns are coming as invalid columns.

I am using sql server 2008 r2.

Any help is most appreciated.

select

ID,Interval,Type,Start,

Case when Type=0 Then 'Daily ' + iif(Interval>1, 'every '+ Interval+' days','') End Recurrence,

CASE WHEN (Pattern % 2) >= 1 THEN 1 ELSE 0 END Sunday,

CASE WHEN (Pattern % 4) >= 2 THEN 1 ELSE 0 END Monday,

CASE WHEN (Pattern % 8) >= 4 THEN 1 ELSE 0 END Thuesday,

CASE WHEN (Pattern % 16) >= 8 THEN 1 ELSE 0 END Wednesday,

CASE WHEN (Pattern % 32) >= 16 THEN 1 ELSE 0 END Thursday,

CASE WHEN (Pattern % 64) >= 32 THEN 1 ELSE 0 END Friday,

CASE WHEN (Pattern % 128) >= 64 THEN 1 ELSE 0 END Saturday from sample table

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

You can't use IIF unless you use 2012. For 2008 and previous versions, you need to use a (nested) CASE.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kish1234 Ten Centuries Points: 1112 More actions · Answer 6

Ahh great..thanks for the tip..

here is the original requirement in the VB script...

Dim Recurrence As string

Select Case RecurrenceType

Case Is = 0

If Interval > 1 Then

Recurrence = "Daily Every " & Interval & " Days"

Else

Recurrence = "Daily"

End if

Case is = 1

If Interval = 0 Then

Recurrence = "Weekly on "

Else

Recurrence = "Every " & Interval & " week on "

End if

And the following is the nested case statement, I have created based on your advise

as am new to case statements, could you please advise where am I making the mistake, as the sql server is throwing

"Msg 245, Level 16, State 1, Line 2

Conversion failed when converting the varchar value 'Daily Every ' to data type tinyint."

Case when Type=0 Then 'Daily '

When Interval > 1 Then 'Daily Every ' + Interval + ' Days' END Recurrence,

Case when RecurrenceType=1 Then 'Weekly on '

When Interval = 0 Then 'Every ' + Interval + ' Weeks on ' END Recurrence,

Thanks for your time in advance.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

You need to cast Interval to a varchar value.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2