473,287 Members | 1,655 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Convert Varchar into Int

Hi

I have imported some varchar figures.

such as 0.050 (we'll call this Field)

I need to convert them into int to be used for calculations.

when I do

CAST(Field as int)

I get...

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '0.050' to a column of data type int.

Normally I dont get this error?

Anyone?
Oct 23 '07 #1
1 33393
Jim Doherty
897 Expert 512MB
Hi

I have imported some varchar figures.

such as 0.050 (we'll call this Field)

I need to convert them into int to be used for calculations.

when I do

CAST(Field as int)

I get...

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '0.050' to a column of data type int.

Normally I dont get this error?

Anyone?
INT datatype (SQL Server Books online (BOL)

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

therefore.... cast(field as decimal(18,3))
or
cast(field as numeric(18,3))

will return

.050

INT will round to the nearest whole number.

If calculating with decimalised data, numeric data precison and scale will demand decimal or numeric datatype

cast in itself cannot explicitly cast a 0.050 'varchar' value to a whole number
therefore inserting any 0.050 value in an integer would round it down to zero (the nearest whole number)


Regards

Jim :)
Oct 25 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

19
by: Lauren Quantrell | last post by:
I have a stored procedure using Convert where the exact same Convert string works in the SELECT portion of the procedure but fails in the WHERE portion. The entire SP is listed below....
2
by: Rudi Groenewald | last post by:
Hi all. If I've got a query which has a field with seconds in it... how will I use the Convert function to get my field converted to the format: HH:MM:SS ? The field with the seconds in is...
4
by: v0lcan0 | last post by:
I’m trying to cast and integer type to a varchar and then concatenate two varchar and ’01’ and convert it into datetime with the following code: CONVERT(datetime, { fn CONCAT({ fn...
15
by: angellian | last post by:
Sorry to raise a stupid question but I tried many methods which did work. how can I conserve the initial zero when I try to convert STR(06) into string in SQL statment? It always gives me 6...
1
by: fatmapatlar | last post by:
I have to convert this script, but i didn't. This is stored procedure of sql. I need to convert to postgresql... if it is possible please help me! Thanks Fatma CREATE PROCEDURE...
2
by: kirke | last post by:
Hi, I have a datetime column named dtDateTime. its format is "Oct 27 2006 12:00:00 " I want to group by only date part of it and count my code is $sql1="SELECT ...
3
by: t2581 | last post by:
Hi , I have database db2 udb 7.2 with following table and datatype CREATE DISTINCT TYPE APP.NOTEPAD AS SYSIBM .CLOB(4096); CREATE TABLE APP.AP_NOTE_PAD ( CONTROL_LOCATION INTEGER NOT NULL...
10
by: satishrajana | last post by:
Hi, My SQL returns a NULL in a datefield if there is no date in that field. If there is a NULL in this column, I want to replace it with spaces in my SELECT statement when I am selecting these...
6
by: tshad | last post by:
Apparently, I can't do: Dim da2 As New OleDb.OleDbDataAdapter("Select PR, Convert(varchar,getchar(),1),F1, F2, F5, Sum(F4) from temp .... I am getting this error. 'undefined function...
0
by: weird0 | last post by:
Here is the code for executing stored procedure ExecuteSP() , and object that sets all its parameters in other functions and the stored procedure. I think it has something to do with db datatypes...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.