Is there a double byte data type 1

[beanNut]

I was tasked with combining data from separate tables that contain text, date and numbers. The customer wants it in a single column. So I did a union of the three tables.

So I chose to union the 3 tables into a single column and cast each type to a universal type. For dates and text that was simple, all I needed to do was cast them into a varchar large enough to handle the data.

However, high precision numbers won't cast into varchar without losing precision. It rounds to 6 significant digits. Hence, 1.123456789123456789 becomes 1.123457.

Can someone suggest a solution?

SELECT
varfloat,
CAST(CAST(varfloat AS float(53)) AS float(53)),
CAST(CAST(varfloat AS varchar(250)) AS float(53))
from testCasting

I'm hoping that you can point me to the correct datatype to use has my union type. Here I cast to varchar(250), but it rounds the value to 1.123457. I think its because its single byte where float(53) was double byte. Is there a double byte data type I can use????

 

[earthandfire]

Try using Decimal as an intermediate as in:

declare @float float (53)
declare @decimal decimal (38, 18)

declare @varchar varchar (1000)

set @float = 1.123456789123456789 

set @varchar = cast(cast(@float as decimal(38,18)) as varchar(100))

select @float
select @decimal
select @varchar

see also BOL:

This table shows the style values for float or real conversion to character data.

Value Output
0 (default) Six digits maximum. Use in scientific notation, when appropriate.
1 Always eight digits. Always use in scientific notation.
2 Always 16 digits. Always use in scientific notation.

Hope this helps.