Convert varchar column to Bit column

[nubianwarrior]

I desperately trying to find a solution to converting a varchar column to a bit column. I'm doing some data conversion between two databases and there is one table that has a varchar in the old database and needs to be converted to a bit in the new database. Can anyone assist with this endeavor?

column1-
clnt_access varchar(3) NOT NULL

needs to be converted to.....

column 2 -
clnt_access BIT, NOT NULL

 

[mrdenny]

I assume that you have a data transfer between the two databases for the table. Change the source from a table to a query and use this query.

SELECT *, case when clnt_access='no' then cast(0 as bit) else cast(1 as bit) end clnt_access2
FROM Table

Then map the new column clnt_access2 to the clnt_access column in the new database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[MDXer]

Another option would be to use a derived column task in your data flow and then parse it and have it enter the bit values, then datatype this column as a bit.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck