Ошибка при преобразовании типа данных 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

33k2 золотых знака29 серебряных знаков59 бронзовых знаков

задан 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,7222 золотых знака14 серебряных знаков39 бронзовых знаков

2

Источник

I have a table with data and one of the columns contains a number stored as text.
When an application updates it, it writes _BAK + datetime stamp behind the number.

I’m now trying to clean up the database by deleting all records that have _BAK in the number column where the most recent one must not be deleted.

id     sitenummer                  
28376  1441_BAK20130213151952032    
28377  1441_BAK20130214142314705

In this case the line with ID 28376 is the oldest and must be removed.

I have created a query that should do just that:

;with sel1 AS (
select t1.ID,t1.sitenummer, CONVERT(BIGint,SUBSTRING(t1.sitenummer,CHARINDEX('_',t1.sitenummer,0)+4,50)) as Stamp1    
from vdfkraan as t1
where t1.sitenummer like '%_BAK%' and (SELECT COUNT(SUBSTRING(t2.sitenummer,0,CHARINDEX('_',t2.sitenummer,0))) FROM vdfkraan as t2 
                                      where SUBSTRING(t1.sitenummer,0,CHARINDEX('_',t1.sitenummer,0))=SUBSTRING(t2.sitenummer,0,CHARINDEX('_',t2.sitenummer,0))) > 1
group by t1.id,t1.sitenummer)

, sel2 AS (
select t3.id, t3.sitenummer, t3.stamp1, 
      (select TOP(1) t4.stamp1 from sel1 as t4
        WHERE SUBSTRING(t4.sitenummer,0,CHARINDEX('_',t4.sitenummer,0)) =SUBSTRING(t3.sitenummer,0,CHARINDEX('_',t3.sitenummer,0))
        order by t3.Stamp1 DESC) AS stamp2 from sel1 as t3)

, sel3 AS (select id from sel2 where Stamp1=stamp2)

--delete FROM vdfkraan
--where id IN (SELECT t1.id FROM sel3 as t1)

--select * from sel2

If I uncomment the last line (select * from sel2), it produces the following table:

id     sitenummer                 stamp1             stamp2
28376  1441_BAK20130213151952032  20130213151952032  20130213151952032
28377  1441_BAK20130214142314705  20130214142314705  20130213151952032

Table sel3 contains one record with one column id = 28376.

So that seems to work just as I want it.

Now I comment the select line and uncomment the Delete lines.

Now I get the following error:

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to bigint.

So without the delete lines, all is ok, no errors, but with it, I get this error.
I have checked the data, there should not be any problem.

What is going on here?

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

  • Question

  • Hi,

    I am using one query in my Stored procedure when i am passing the values i am getting the error like Error converting data type varchar to bigint.

    select @BasicPrice = convert(float, rate_dc) from RENAULT.TMFSCBaseValue
    where PKConditionType_vc = ‘ZPRS’ and PKSalesDocType_vc = ‘YP’ and
    PKDistributionChannel_vc = ’01’ and PKDivision_vc = ’91’ 
    and  PKServiceNo_vc = @ServiceNo   and PKModelCode_vc = @ModelCode 

    and  convert(bigint,@InvoiceDate) between  convert(bigint,PKValidityStartDate_dt)

    and convert(bigint,PKValidityEndDate_dt) 

    Input values

    @DealerCode :: LN010391
    @VehSrNo :: 9ZJ16246
    @ModelCode :: LP11PB15RO11WD
    @ServiceNo :: 1
    @InvoiceDate :: 09/10/20
    @ServiceDate :: 24/06/20

    in table  TMFSCBaseValue  PKValidityStartDate_dt data type id varchar

    Please help someone  on this

    Ramana

Answers

  • Change the format of @ServiceDate :: 24/06/20

    Currently, it is being passed in the format dd/mm/yy. Instead, make it as
    mm/dd/yy and you are done.

    Hope, this helps !

    -Vinay Pugalia
    If a post answers your question, please click «Mark As Answer» on that post or
    «Vote as Helpful».
    Web : Inkey Solutions

    Blog : My Blog
    Email : Vinay Pugalia

    • Marked as answer by

      Tuesday, June 28, 2011 11:35 AM

Источник
  • 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

Источник

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.

Источник

  • Ошибка при преобразовании типа данных nvarchar к datetime
  • Ошибка при преобразовании типа данных nvarchar к bigint
  • Ошибка при преобразовании типа данных float к decimal
  • Ошибка при предоставлении общего доступа общий ресурс не создан windows 10
  • Ошибка при предоставлении общего доступа к папке