T-SQL Arithmetic overflow error converting GUID to any other data type

Question

I have a query where I'm trying to concatenate a GUID to a string. No matter what I do, or what I try to convert the GUID to, I get an arithmetic overflow error. Even this:

select CAST(NEWID() as nvarchar)

Returns:

Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type nvarchar

All similar questions have been resolved by a CAST or CONVERT but this always returns an error. Same result for CHAR or VARCHAR. Is there a setting I have to change to make this work or something?

Mitch Wheat · Accepted Answer · 2021-01-30 02:24:31Z

4

You've fallen into the trap of not specifying a length for your nvarchar. What you want is:

select CAST(NEWID() as nvarchar(36))

[ GUIDs are rendered as 36 character strings ]

When length is unspecified, SQL Server uses a default length of 30 for varchar/nvarchar when used in CAST and CONVERT functions.

You should ALWAYS explicitly specify your string length.

edited Jan 30, 2021 at 2:24

answered Jan 30, 2021 at 2:19

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oliver Schimmer Over a year ago

This was very valuable info, thanks. I always assumed not specifying the length in a CONVERT or CAST would just mean 'use whichever length works'.

Collectives™ on Stack Overflow

T-SQL Arithmetic overflow error converting GUID to any other data type

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related