SQL Server - Error converting data type float to bigint

Question

Can the datatype of a field be changed to bigint from float ??

Create a new column that's an integer:

ALTER TABLE tabl ADD newCol int;

Select the data from the old column into the new one:

UPDATE tabl SET newCol = CAST(TEL2 AS int) FROM tabl ;

P.S: I am using MS SQL Server

i try to us this in c# but it didn't get any result

  string sqlCommand = "SELECT * FROM tabl WHERE CONCAT([TEL1], [TEL2],[TEL3], [TEL4],[TEL5], [TEL6],[TEL7], [TEL8],[TEL9]) LIKE '%" + txtBoxSearch.Text + "%'";

I store telephone numbers as float and try find it by the c# code and didn't get any result so im trying to convert the column or correct the c# code

It is not clear where is your problem. Where are the FLOAT and BIGINT in your table? What has to do the final query with your problem? Do you store telephone numbers as float? — Steve, Mar 24 '19 at 11:19
See here why your choice to store tel numbers as float is wrong https://stackoverflow.com/questions/3483156/whats-the-right-way-to-represent-phone-numbers — Steve, Mar 24 '19 at 11:26
If you are able to change the table then make phone numbers varchar instead of numeric. — Crowcoder, Mar 24 '19 at 11:26
@ahmed then I am confused by the statements about converting them to bigint. — Crowcoder, Mar 24 '19 at 11:29
@Crowcoder i need to convert it to any thing to us it bout i can't — ahmed_eltot93, Mar 24 '19 at 11:31
Add another column, convert the floats to strings and then check what happened in the converted column. Perhaps you can recover something. But sooner or later you need to fix this error. — Steve, Mar 24 '19 at 11:32
@tylkonachwile i try everything in google and stack overflow and i didn't find any thing — ahmed_eltot93, Mar 24 '19 at 11:35
Use this in your SQL string to convert your float to varchar: `SELECT STR(float_field, 25, 0)`. For more information, refer to STR() function document: https://learn.microsoft.com/en-us/sql/t-sql/functions/str-transact-sql?view=sql-server-2017 — Rahul Sharma, Mar 24 '19 at 11:44
@RahulSharma ok it good bout it make a new column not convert it — ahmed_eltot93, Mar 24 '19 at 11:52
What do you mean by that? I am not quite sure what is your problem here? — Rahul Sharma, Mar 24 '19 at 11:54
@RahulSharma it work in sql server bout it not working in my c# code — ahmed_eltot93, Mar 24 '19 at 12:08

score 2 · Accepted Answer · answered Mar 24 '19 at 13:31

Try using the STR() function:

SELECT STR(your_float_field, 25, 0)

This would return your float value as a string. Note: This function pads on the left with spaces. If this is a problem, combine with LTRIM function:

SELECT LTRIM(STR(your_float_field, 25, 0))

score -1 · Answer 2 · answered Mar 24 '19 at 11:32

-1

No,because float contain decimals And bigint contain only integer. But bigint can be converted into Float as float contain both part -integer and decimals. If your float value contains .00 as the decimal value then it can be changed into bigint. Thanks. If you want more about datatype conversion.then please comment. Thanks again.

answered Mar 24 '19 at 11:32

krishan -programmer in india

11
3

Are you understanding the above answer. – krishan -programmer in india Mar 24 '19 at 11:36
yes i understand that . now it is a number (21412124) when i convert it to nvarchar it be like (2.1412e+) – ahmed_eltot93 Mar 24 '19 at 11:39
This is because mysql convert the no. According to it's unicode character. – krishan -programmer in india Mar 24 '19 at 11:44

SQL Server - Error converting data type float to bigint

2 Answers2