Why does SQL Server convert floats to scientific notation?

4 votes

2 answers

31926 views

sql-server number-formatting floating-point

                          I came across some weird behavior: While passing a float value into a varchar column, the values are getting converted from integers into scientific notation, and it's that scientific notation that gets stored as a string.

		if OBJECT_Id('tempdb..#whydis') is not null begin drop table #whydis end
		if OBJECT_Id('tempdb..#ImSeriously') is not null begin drop table #ImSeriously end

		create table #whydis (bigID float)
		create table #ImSeriously (bigID varchar(255))

		insert into #whydis(BigID)
		values(1495591),
		(1495289),
		(1495610),
		(1495611),
		(1495609),
		(1495592),
		(1495686)

		INSERT INTO #ImSeriously (bigID)
		SELECT BigID from #whydis

		select * from #ImSeriously

results look like this:

1.49559e+006

Scientific notation stored as a string. It's easy enough to work around by casting as int:

		INSERT INTO #ImSeriously (bigID)
		SELECT cast(BigID as int) from #whydis

But the whole thing has me scratching my head.

**Question:** What is it about floats that stores them this way?

                        

Asked by James (2668 rep)

Jun 28, 2021, 03:28 PM
Last activity: Jun 28, 2021, 04:22 PM

Why does SQL Server convert floats to scientific notation?

Related Questions