Insufficient result space to convert uniqueidentifier value to char

Recently when I was trying to convert a UNIQUEIDENTIFIER to CHAR, I got an error like 

Insufficient result space to convert uniqueidentifier value to char  

My statement was something like 

SELECT CONVERT(CHAR, NEWID()) 

I have faced this issue many times and resolved it too but keep on forgetting the fix. And every time I had to surf the web for fix. So thought of logging it in my blog. 

When you convert from one data type to another (whether you use CAST or CONVERT), most of the time no need to specify the length of the data type.  

Example when you convert a DATE to CHAR, no need to mention the length. Simply say which data type you want to convert. In the present case, CHAR. 

SELECT CONVERT(CHAR, GETDATE(), 110) 

This is same when you convert from numeric to char etc.. 

But when UNIQUEIDENTIFIER is converted to string, then you need to mention the length explicitly.  The length of the UNIQUEIDENTIFIER after conversion is 36 bytes.  

So our statement should like 

SELECT CONVERT(CHAR(36), NEWID())

Advertisements

About Suresh Kumar D

Hardcore SQL Developer and aspiring Database Architect.
This entry was posted in T-SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s