Меню

Ошибка при преобразовании типа данных varchar к bigint sql

SELECT
  #dp_all.RegistrationNumber AS 'номер устройства',
  #dp_all.DeviceLocation AS 'Номер ТС',
  MAX(#card_transactions.timestamp) AS 'дата валидации',
  #dp_all.LastVersionReadTime AS 'дата связи'
FROM
  #dp_all
  INNER JOIN #card_transactions ON
    #dp_all.RegistrationNumber like '%' + #card_transactions.device_reg_no  
WHERE
  #dp_all.DeviceLocation  is not null
GROUP BY
  #dp_all.RegistrationNumber,
  #dp_all.DeviceLocation,
  #dp_all.LastVersionReadTime
ORDER BY
  #dp_all.DeviceLocation

Где:

  • RegistrationNumber -bigint,
  • DeviceLocation — nvarchar,
  • LastVersionReadTime — datetime,
  • derice_reg_no — bigint.

Anton Shchyrov's user avatar

Anton Shchyrov

32.8k2 золотых знака28 серебряных знаков56 бронзовых знаков

задан 11 сен 2018 в 13:13

7

Есть такая штука Data type precedence. Именно из-за приоритетов вы получаете ошибку при сравнении, уже неявно приобразованной строки с bigint значением.

Второй момент, сравнивать эти поля стоит тогда когда они оба будут одного типа. Преведите первый в varchar(n)/nvarchar(n) и второй с помощью CAST или CONVERT и сравнивайте вашим подходом наздоровье.

Пример условия:

ON convert(nvarchar,#dp_all.RegistrationNumber) like '%' + convert(nvarchar,#card_transactions.device_reg_no)

ответ дан 11 сен 2018 в 13:41

Nick Proskuryakov's user avatar

Nick ProskuryakovNick Proskuryakov

3,7122 золотых знака13 серебряных знаков37 бронзовых знаков

2

Источник

I have designed a SP where i need to update all the records for a table where ErrorId is not equal to the ones provided.In this stored procedure i am parsing and all the errorids delimited by ‘,’ into a varchar variable which i would be using for updating the table.On the second last line i get the error mentioned in the subject line.any help would be appreciated.

ALTER PROCEDURE [dbo].[sp_ParseAndUpdateDetails]

@NozzleID int,

@ParserString varchar(MAX)

AS

BEGIN

DECLARE @NextPos int

DECLARE @LoopCond tinyint

DECLARE @PreviousPos int

DECLARE @FlgFirst bit

DECLARE @QueryCondition varchar(MAX)

SET @LoopCond=1

SET @NextPos =0

SET @FlgFirst=0

SET @QueryCondition=»

WHILE (@LoopCond=1)

BEGIN

—Retrieving the Position of the delimiter

SET @NextPos =@NextPos + 1

SET @NextPos = CHARINDEX(‘,’,@ParserString, @NextPos)

—Retreiving the last substring

IF(@NextPos=0)

BEGIN

PRINT SUBSTRING(@ParserString,@PreviousPos + 1,(LEN(@ParserString)+1)- @PreviousPos)

SET @QueryCondition= @QueryCondition + ‘ AND ErrorId <> ‘ + CAST(SUBSTRING(@ParserString,@PreviousPos + 1,(LEN(@ParserString)+1)- @PreviousPos) AS bigint)

SET @PreviousPos = @NextPos

BREAK

END

—Retrieving the individual substrings

If @FlgFirst=0

—Retreiving the first substring

BEGIN

SET @FlgFirst=1

PRINT SUBSTRING(@ParserString,1, @NextPos—1)

SET @QueryCondition= @QueryCondition + CAST(SUBSTRING(@ParserString,1, @NextPos—1) AS bigint)

SET @PreviousPos = @NextPos

END

ELSE

—Retreiving the internmediate substrings

BEGIN

PRINT SUBSTRING(@ParserString,@PreviousPos + 1,(@NextPos—1)-@PreviousPos)

SET @QueryCondition= @QueryCondition + ‘ AND ErrorId <> ‘ + CAST(SUBSTRING(@ParserString,@PreviousPos + 1,(@NextPos—1)-@PreviousPos) AS bigint)

SET @PreviousPos = @NextPos

END

END

print ‘ErrorId <>’ + @QueryCondition

UPDATE [ESMS2_DBMS].[dbo].[ErrorDetails]

SET ErrorRectifyDateTime=GETDATE()

WHERE (NozzleId = @NozzleId) AND (ErrorRectifyDateTime IS NULL) AND (ErrorId <> @QueryCondition)

END

Источник
  • Remove From My Forums

  • Вопрос

  • Hi,

    I am a newbie user of MS SQL. I’m creating this procedure and been searching for the right solution to this problem for so long. I really need help this time! When I compile the procedure, it was fine but when I try to execute it like:

    EXEC TA_COPY_TKT_DB 201166573491, 201166573491, ‘MSSQLSERVERDEV’, ‘MSSQLSERVERDEV’, ‘Demo84’, ‘Demo841’

    I get this error:

    Msg 8114, Level 16, State 5, Procedure TA_COPY_TKT_DB, Line 24

    Error converting data type varchar to bigint.

    Here’s the whole procedure I created:

    ALTER PROCEDURE [dbo].[TA_COPY_TKT_DB]

    @FromDocID
    T_DOC_ID,

    @ToDocID
    T_DOC_ID,

    @FromServerName
    VARCHAR(50),

    @ToServerName
    VARCHAR(50),

    @FromDatabaseName
    VARCHAR(50),

    @ToDatabaseName
    VARCHAR(50)

    as

    begin

      Declare

    @SqlStmt
    VARCHAR(150)

      Set Nocount On

    /* Check PS_DOC_HDR if exists */

    Begin Tran

    EXEC USP_DEL_TKT @FromDocID

    print @ToDocID

    print @FromDocID

    Set @SqlStmt = ‘INSERT INTO ‘ + @ToServerName + ‘.’ + @ToDatabaseName + ‘.dbo.PS_DOC_HDR ‘ +

      ‘SELECT * FROM ‘ + @FromServerName + ‘.’ + @FromDatabaseName + ‘.dbo.PS_DOC_HDR WHERE DOC_ID = ‘ + convert(bigint,@FromDocID)

    Commit Tran

    end

Ответы

  • Your last statement is of the form

    Set @SqlStmt = <some concatened strings> + convert(bigint,@FromDocID)

    But the result of convert(bigint, @FromDocID) is (of course) a bigint.  So you have a string type and a bigint type.  Bigint has a higher priority then varchar, so SQL will try to convert the concatened strings to a bigint, which it can’t do so you
    get the error.

    So you must make the convert(bigint, @FromDocID) into a varchar (or char) type.  Since you declared it as a T_DOC_ID datatype and didn’t tell us what T_DOC_ID really is, it’s hard to know exactly what you want.  Some possibilities

    If T_DOC_ID is an integer type (tiny int, smallint, int, bigint) then

    Set @SqlStmt = <some concatened strings> + convert(varchar(20),@FromDocID)

    If T_DOC_ID is something else then

    Set @SqlStmt = <some concatened strings> + convert(varchar(20),convert(bigint, @FromDocID))

    Tom

    • Помечено в качестве ответа

      2 января 2012 г. 4:30

Источник
  • Remove From My Forums

  • Question

  • Dear

          I have the issue with my SQL. I used the a variable with bigint to pass into my Select query. When I run this SQL, I received this error message «Error converting data type varchar to bigint». How can I resolve this
    issue?

    Thanks

    Khoi

Answers

  • Valiant1982,

    You are running into this issue because of data type precedence.  Essentially, when data is compared in sql server, data types  that do not match have to be converted into the same type.  This is known as implicit conversion.  In
    order to do this, SQL Server uses precedence rules, which only mean the data type with the greatest precedence, wins and the other data type must be converted.  In your case BIGINT takes precedence over a character column therefore, SQL Server has to
    do an implicit conversion of your character column to BIGINT.  If any of the data in the column cant convert, you get a conversion error.

    Your options are to change the data type of your variable to match your column data type, or to clean your data up.  You can find the bad data using the query below.  Please note that you should consider changing your column data type to bigint,
    if this is what you plan to use for comparison.

    DECLARE @t TABLE(ID VARCHAR(10));

INSERT INTO @t VALUES ('1');
INSERT INTO @t VALUES ('1A');
INSERT INTO @t VALUES ('1-)');
INSERT INTO @t VALUES ('1?41');
INSERT INTO @t VALUES ('2');
INSERT INTO @t VALUES ('3');

SELECT * FROM @t WHERE Id LIKE '%[^0-9]%'

    http://jahaines.blogspot.com/

    • Proposed as answer by

      Wednesday, July 14, 2010 5:59 PM

    • Marked as answer by
      KJian_
      Tuesday, July 20, 2010 12:25 PM

Источник
  • Remove From My Forums

  • Question

  • Dear

          I have the issue with my SQL. I used the a variable with bigint to pass into my Select query. When I run this SQL, I received this error message «Error converting data type varchar to bigint». How can I resolve this
    issue?

    Thanks

    Khoi

Answers

  • Valiant1982,

    You are running into this issue because of data type precedence.  Essentially, when data is compared in sql server, data types  that do not match have to be converted into the same type.  This is known as implicit conversion.  In
    order to do this, SQL Server uses precedence rules, which only mean the data type with the greatest precedence, wins and the other data type must be converted.  In your case BIGINT takes precedence over a character column therefore, SQL Server has to
    do an implicit conversion of your character column to BIGINT.  If any of the data in the column cant convert, you get a conversion error.

    Your options are to change the data type of your variable to match your column data type, or to clean your data up.  You can find the bad data using the query below.  Please note that you should consider changing your column data type to bigint,
    if this is what you plan to use for comparison.

    DECLARE @t TABLE(ID VARCHAR(10));

INSERT INTO @t VALUES ('1');
INSERT INTO @t VALUES ('1A');
INSERT INTO @t VALUES ('1-)');
INSERT INTO @t VALUES ('1?41');
INSERT INTO @t VALUES ('2');
INSERT INTO @t VALUES ('3');

SELECT * FROM @t WHERE Id LIKE '%[^0-9]%'

    http://jahaines.blogspot.com/

    • Proposed as answer by

      Wednesday, July 14, 2010 5:59 PM

    • Marked as answer by
      KJian_
      Tuesday, July 20, 2010 12:25 PM

Источник
  • Remove From My Forums

  • Question

  •  SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = string.Format(«select * from EmployeeType where id=’Hierachy Update'», Id);

                SqlDataReader dataReader = command.ExecuteReader();

                if (dataReader.Read())
                {
                    Employee = new EmployeeType();
                    Employee.Id = Convert.ToInt64(dataReader[«Id»]);
                    Employee.Type = Convert.ToString(dataReader[«Type»]);

                }

Answers

  •             command.Connection = connection;
                command.CommandText = string.Format(«select * from EmployeeType where id=’Hierachy Update'», Id);

    I think your intent is to pass Id as the WHERE clause value.  I suggest a parameterized query like the example below.

                SqlCommand command = new SqlCommand("select * from EmployeeType where id=@id", connection);
            command.Parameters.Add("@id", SqlDbType.BigInt).Value = Id;

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by

      Monday, April 7, 2014 2:23 AM

    • Marked as answer by
      Elvis Long
      Thursday, April 17, 2014 6:41 AM

    • Edited by
      Kalman Toth
      Sunday, April 6, 2014 6:08 AM
    • Proposed as answer by
      Naomi N
      Monday, April 7, 2014 2:23 AM
    • Marked as answer by
      Elvis Long
      Thursday, April 17, 2014 6:41 AM
Источник
  • Remove From My Forums

  • Question

  •  SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = string.Format(«select * from EmployeeType where id=’Hierachy Update'», Id);

                SqlDataReader dataReader = command.ExecuteReader();

                if (dataReader.Read())
                {
                    Employee = new EmployeeType();
                    Employee.Id = Convert.ToInt64(dataReader[«Id»]);
                    Employee.Type = Convert.ToString(dataReader[«Type»]);

                }

Answers

  •             command.Connection = connection;
                command.CommandText = string.Format(«select * from EmployeeType where id=’Hierachy Update'», Id);

    I think your intent is to pass Id as the WHERE clause value.  I suggest a parameterized query like the example below.

                SqlCommand command = new SqlCommand("select * from EmployeeType where id=@id", connection);
            command.Parameters.Add("@id", SqlDbType.BigInt).Value = Id;

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by

      Monday, April 7, 2014 2:23 AM

    • Marked as answer by
      Elvis Long
      Thursday, April 17, 2014 6:41 AM

    • Edited by
      Kalman Toth
      Sunday, April 6, 2014 6:08 AM
    • Proposed as answer by
      Naomi N
      Monday, April 7, 2014 2:23 AM
    • Marked as answer by
      Elvis Long
      Thursday, April 17, 2014 6:41 AM
Источник

strees

0 / 0 / 0

Регистрация: 20.12.2013

Сообщений: 2

1

20.12.2013, 18:33. Показов 10538. Ответов 2

Метки нет (Все метки)

таблица

T-SQL 
1
2
3
4
5
6
7

create table поставщик
(код_поставщика int not null primary key identity(1,1),
телефон varchar(50) not null,
адрес varchar(50) not null,
наименование_поставщика varchar(50) not null,
к_кому_обращаться varchar(50) not null,
unique (код_поставщика))

процедура

T-SQL 
1
2
3
4
5
6
7
8
9

create procedure poisk1 
@Ведите_товар bigint
as 
begin 
set nocount on;
select к_кому_обращаться = наименование_поставщика
from поставщик
where наименование_поставщика  = @Ведите_товар
END

ошибка:
Ошибка при преобразовании типа данных nvarchar к bigint.

(строк обработано: 1)

__________________
Помощь в написании контрольных, курсовых и дипломных работ, диссертаций здесь



0

1561 / 1113 / 164

Регистрация: 23.07.2010

Сообщений: 6,382

20.12.2013, 20:30

2

Цитата
Сообщение от strees
Посмотреть сообщение

where наименование_поставщика = @Ведите_товар

ужос
varchar(50) и bigint?

Добавлено через 58 секунд

Цитата
Сообщение от strees
Посмотреть сообщение

Ошибка при преобразовании типа данных nvarchar к bigint.

рвет на квадраты



0

StudentMichael

20 / 20 / 1

Регистрация: 03.01.2013

Сообщений: 184

23.12.2013, 08:23

3

Цитата
Сообщение от pincet
Посмотреть сообщение

рвет на квадраты

Ой хорош)))))

Во-первых, зачем тебе bigint? инфа сотка хватит int
Во-вторых, ужасные русские имена колонок… trystory пиши на англ.
В-третьих, для преобразования nvarchar и bigint использую cast

SQL 
1

CAST(yourColumn AS data_type)



0
Источник

farmerregistration table as follows

farmerid datatype Varchar(50) in farmerregistration table

farmerid Firstname Region Zone Section Village

1055662 Lacina OUNGAlo Diawala Nord Diwala

transaction table as follows

transactionid datatype Bigint in transaction table

transactionid Qty Price Paid Due

1055662 1 200 200 100

from the above i want output as follows

Firstname Region Zone Section Qty Price Paid

Lacina OUNGALo Diawala Nord 1 200 200

My query as follows

select a.firstname,a.Region,a.Zone,a.Section,b.Qty,b.Price,b.paid
from farmerregistration a,
transaction b where a.transactionid = b.farmerid

Note in farmerregistration table farmerid datatype is varchar(50)
in transaction table transactionid datatype is bigint

when i run the above code shows error as follows

Error converting data type varchar to bigint.

how to solve this error. from my above query what changes i have to made.

What I have tried:

farmerregistration table as follows

farmerid datatype Varchar(50) in farmerregistration table

farmerid Firstname Region Zone Section Village

1055662 Lacina OUNGAlo Diawala Nord Diwala

transaction table as follows

transactionid datatype Bigint in transaction table

transactionid Qty Price Paid Due

1055662 1 200 200 100

from the above i want output as follows

Firstname Region Zone Section Qty Price Paid

Lacina OUNGALo Diawala Nord 1 200 200

My query as follows

select a.firstname,a.Region,a.Zone,a.Section,b.Qty,b.Price,b.paid
from farmerregistration a,
transaction b where a.transactionid = b.farmerid

Note in farmerregistration table farmerid datatype is varchar(50)
in transaction table transactionid datatype is bigint

when i run the above code shows error as follows

Error converting data type varchar to bigint.

how to solve this error. from my above query what changes i have to made.

Источник

0 0 голоса
Рейтинг статьи
Подписаться
Уведомить о
guest

0 комментариев
Старые
Новые Популярные
Межтекстовые Отзывы
Посмотреть все комментарии

А вот еще интересные материалы:

  • Яшка сломя голову остановился исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного где ошибка
  • Ошибка при преобразовании типа данных nvarchar к float sql
  • Ошибка при преобразовании типа данных nvarchar к datetime

    • ➤