Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL, How to convert VARCHAR to bigint?

Tags:

sql

sql-server

I have a field that is VARCHAR(6) I am trying to insert it into another table of type bigint it is giving me an error

(Error Converting from data type varchar to bigint

here is what i am doing

CONVERT(bigint, seconds) as seconds 

Can anybody help with this issue?

like image 274
Jaylen Avatar asked Jan 31 '13 22:01

Jaylen


People also ask

Can we convert varchar to int in SQL?

SQL Server's CAST() and CONVERT() methods can be used to convert VARCHAR to INT. We'll also look at the more efficient and secure approach to transform values from one data type to another.

How do I convert varchar to numeric in SQL?

To convert a varchar type to a numeric type, change the target type as numeric or BIGNUMERIC as shown in the example below: SELECT CAST('344' AS NUMERIC) AS NUMERIC; SELECT CAST('344' AS BIGNUMERIC) AS big_numeric; The queries above should return the specified value converted to numeric and big numeric.

What is CAST () and convert () functions in SQL Server?

The cast and convert functions provide similar functionality. They are used to convert a value from one data type to another. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

What is BigInt SQL?

What is a BigInt? The BigInt data type in SQL Server is the 64-bit representation of an integer. It takes up 8 bytes of storage. It can range from -2^63 (-9,223,372,036,854,775,808) to 2^63 (9,223,372,036,854,775,807). Two raised to the power of sixty-three is about nine quintillion, a very big number.


2 Answers

This is the answer

(CASE   WHEN     (isnumeric(ts.TimeInSeconds) = 1)    THEN     CAST(ts.TimeInSeconds AS bigint)   ELSE     0   END) AS seconds 
like image 51
Jaylen Avatar answered Sep 19 '22 15:09

Jaylen


an alternative would be to do something like:

SELECT    CAST(P0.seconds as bigint) as seconds FROM    (    SELECT       seconds    FROM       TableName    WHERE       ISNUMERIC(seconds) = 1    ) P0 
like image 34
SQLGuru Avatar answered Sep 19 '22 15:09

SQLGuru