Solved

SQL Server Convert or Cast function

Posted on 2012-03-22
8
445 Views
Last Modified: 2012-03-27
I am trying to convert a decimal into a hexadecimal string in a table.  I can run the select query just fine (Select convert(varbinary(8), account) from accountTemp) but cannot run either an update on the table or a new insert to store the value permanently. (update accountTemp set account = convert(varbinary(8), account) -- OR -- Insert into account select convert(varbinary(8),account) from accountTbl

Any suggestions as to how I can have this be converted and stored into the table?
0
Comment
Question by:PhillipsPlastics
8 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 37752608
Try doing it through a variable?


declare @tmp VarBinary(8)
set @tmp = (Select convert(varbinary(8), account) from accountTemp)
update accountTemp set account @tmp
where
-- ...  fill in where ... --
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 37752611
Look at the definition of Convert. You're changing the type from whatever Account is into a varbinary type. I'll bet you Account is not a varbinary type of field!

Take a look at http://rextang.net/blogs/work/archive/2008/01/13/5855.aspx for info on converting your varbinary out into hex.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37752658
Please try:

master.dbo.fn_varbintohexstr

For example:

SELECT master.dbo.fn_varbintohexstr(convert(varbinary(8), account))
from accountTemp
0
 

Author Comment

by:PhillipsPlastics
ID: 37753087
Well this is what I ended up doing since nothing else was working...

declare @tmp varbinary(8);
set @tmp = (CONVERT(varbinary(8),66082));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 66082;
set @tmp = (CONVERT(varbinary(8),514));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 514;
set @tmp = (CONVERT(varbinary(8),544));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 544;
set @tmp = (CONVERT(varbinary(8),512));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 512;
set @tmp = (CONVERT(varbinary(8),66080));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 66080;
set @tmp = (CONVERT(varbinary(8),66048));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 66048;
set @tmp = (CONVERT(varbinary(8),546));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 546;
set @tmp = (CONVERT(varbinary(8),66050));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 66050;
select * from phonesearch_db.dbo.accountTemp;

Open in new window

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 16

Expert Comment

by:DcpKing
ID: 37753228
Did fn_varbintohexstr not work for you?
0
 

Accepted Solution

by:
PhillipsPlastics earned 0 total points
ID: 37753405
It seemed to do the same thing as convert but what I am actually ending up doing is setting the column account to be varbinary(8) and when I read the data into the table it automatically converts the decimal to hex.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 37754514
Clever! And there we all were assuming that you needed to keep the original value. Thanks for wrapping it up.
0
 

Author Closing Comment

by:PhillipsPlastics
ID: 37770419
None of the other options listed worked.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now