Error converting data type varchar to bigint перевод

CASE SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr)) 
WHEN '-' THEN NULL 
ELSE CONVERT(bigint, SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr))) 
END

{
         "lat": "52.650000",
         "lon": "5.730000",
         "name": "NAGELE",
         "cmt": "PROV: FLEVOLAND, INW: -",
         "desc": "PROV: FLEVOLAND, INW: -",
         "sym": "Medium City"
      },
      {
         "lat": "52.670000",
         "lon": "5.600000",
         "name": "URK",
         "cmt": "PROV: FLEVOLAND, INW: 16489",
         "desc": "PROV: FLEVOLAND, INW: 16489",
         "sym": "Medium City"
      },

CASE SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr)) 
WHEN '-' THEN NULL 
ELSE CAST(SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr)) AS bigint) 
END

begin
            declare @_StateProvinceID int = (NEXT VALUE FOR [Sequences].[StateProvinceID])
            Insert into [Application].[StateProvinces] (StateProvinceID,StateProvinceCode,StateProvinceName,CountryID,SalesTerritory)
            values(@_StateProvinceID,UPPER(SUBSTRING (@StateProvince, 1, 2)),@StateProvince,@_CountryID,@_SalesTerritory)

            declare @_InnerJson VARCHAR(MAX) = @json
            DECLARE @wpt VARCHAR(MAX);
            SET @wpt = JSON_QUERY(@_InnerJson, '$.wpt');

            ;with cte_a as (
            SELECT UPPER(SUBSTRING (@StateProvince, 1, 2))[StateProvinceCode],@StateProvince [StateProvince],* FROM  
            OPENJSON ( @wpt )  
            WITH (name varchar(max) '$.name',lat varchar(max) '$.lat',lon varchar(max) '$.lon', descr varchar(max) '$.desc'))

            Insert Into [Application].[Cities] (StateProvinceID,CityName,Location,LatestRecordedPopulation)
            select @_StateProvinceID,a.name,geography::Point(lat, lon, 4326),
                (CASE (SELECT SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr))) WHEN '-' THEN NULL ELSE (SELECT SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr))) END)
            from cte_a a
        End

3 ответа

Лучший ответ

SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr)) 

CAST(
    NULLIF(
        SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr)),
        '-'
    )
    AS BIGINT
)

'>' + SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr)) + '<' 

Error converting data type varchar to bigint.

CASE SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr)) 
WHEN '-' THEN NULL 
ELSE TRY_CONVERT(bigint, SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr))) 
END

CASE SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr)) 
WHEN '-' THEN NULL 
ELSE CONVERT(bigint, SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr))) 
END

CASE SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, 1) 
WHEN '-' THEN NULL 
ELSE CONVERT(bigint, SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, 5)) 
END

CASE SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, 1) 
WHEN '-' THEN NULL 
ELSE CONVERT(bigint, SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, CHARINDEX('",', @someString,CHARINDEX('INW: ', @someString)) - CHARINDEX('INW: ', @someString) - 5))) 
END

Sql error converting varchar to bigint

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

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]

@ParserString varchar ( MAX )

DECLARE @NextPos int

DECLARE @LoopCond tinyint

DECLARE @PreviousPos int

DECLARE @FlgFirst bit

DECLARE @QueryCondition varchar ( MAX )

SET @LoopCond = 1

SET @FlgFirst = 0

WHILE ( @LoopCond = 1 )

—Retrieving the Position of the delimiter

SET @NextPos = @NextPos + 1

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

—Retreiving the last substring

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

—Retrieving the individual substrings

—Retreiving the first substring

SET @FlgFirst = 1

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

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

SET @PreviousPos = @NextPos

—Retreiving the internmediate substrings

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

print ‘ErrorId <>‘ + @QueryCondition

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

SET ErrorRectifyDateTime = GETDATE ()

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

Answers

Thanks friends for your replies

Finally following is the dynamic sql modified statement.

SELECT @Sql = ‘UPDATE [ESMS2_DBMS].[dbo].[ErrorDetails] ‘

SELECT @Sql = @Sql + ‘ SET ErrorRectifyDateTime = CONVERT(VARCHAR(25),GETDATE())’

SELECT @Sql = @Sql + ‘ WHERE (NozzleId = ‘ + CONVERT ( VARCHAR ( 20 ), @NozzleId ) + ‘) AND (ErrorRectifyDateTime IS NULL)’

SELECT @Sql = @Sql + ‘ AND (ErrorId NOT IN (SELECT ErrorId FROM [ESMS2_DBMS].[dbo].[‘ + @TempTable + ‘])) ‘

EXEC sp_executesql @Sql

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

Why would you cast the string value as bigint, then concatenate it back with a varchar data type variable? This forces SQL to do implicit data type conversions.

I’m guessing the data type for the ErrorId column is bigint. SQL cannot convert @QueryCondition from varchar to bigint b/c the variable contains characters.

Источник

Sql error converting varchar to bigint

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I have a table where the values of two columns, while saved as a four byte integer, have different real meanings depending upon the contents of a third column. Sometimes the value is an int, float, one of multiple enumerated values, version information and so on. To handle this, there are case statements within the select and this works just fine for discriminating between the various record types.

The int and float conversion code works just fine but when an enumerated value is included Sql Server has a problem with things and displays ‘Error converting data type varchar to bigint.’

The SQL script (sort of) looks like this. At the time of the select the RecordId, RecordType and DataVal2 are taken as is by the select. However, DataVal1 gets converted depending upon the contents of RecordType. In this case, a 0 indicates that DataVal1 needs to be passed to a stored procedure and the results of that (which are a float) are placed in that column of the row. Alternatively, if RecordType is a 3 then DataVal1 is an enumerated value and the script uses that as a key to get the human friendly string that corresponds to the enumerated value that corresponds to the int in DataVal1. If the RecordType is some else then the default case is called and DataVal1 comes through unmodified as an int.

So what I am guessing is happening is that the first values to come through are interpreted as an int. When the enumerated value converted to a string comes along the DataVal1 column has already been established as an int. If this assessment is correct then it would seem that the simplest solution would be to convert everything to a string. How can that be done?

Источник

Converting VARCHAR to BIGINT

February 25, 2016 at 12:03 pm

I am working on a project where I need to move values from one table (tableOriginal) into another table (tableNew). The original table has two columns EstCards / EstAccts that are stored as varchar but in the new table, we want these values stored as integer (technically bigint because some values in the original table for some reason are in the billions).

When I do the following SQL Statement (see pasted below), I am getting an error saying (this will be added to the SELECT INTO statement but for now just need to convert the varchar values to bigint and if they cannot be converted, enter zero in the new table):

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to bigint.

I am not sure what record this is breaking on to know what I am missing. Do you know of a way to check if a varchar can be converted to a number and if it cannot, I want to insert a zero or even know how to find the record that is failing the test? I really only think I need only one “WHEN” statement, but I am trying anything I can think of.

WHEN EstCards IS NULL THEN 0

WHEN LEN(EstCards) = 0 THEN 0

WHEN ISNUMERIC(EstCards) = 0 THEN 0

WHEN ISNUMERIC(EstCards) = 1 THEN CAST(EstCards AS BIGINT)

WHEN EstAccounts IS NULL THEN 0

WHEN LEN(EstAccounts) = 0 THEN 0

WHEN ISNUMERIC(EstAccounts) = 0 THEN 0

WHEN ISNUMERIC(EstAccounts) = 1 THEN CAST(EstAccounts AS BIGINT)

Источник

Error converting data type varchar to bigint.

Hello. I am creating a SP and receiving the error message while executing. This SP works fine on different servers but it cause issue on this server only.

DECLARE @HOSTNAME VARCHAR(20),
@iatebreakfast VARCHAR(100),
@BGCOLOR VARCHAR(50),
@REC VARCHAR(50),
@Priority Networks VARCHAR(10),
@free VARCHAR(20),
@total VARCHAR(20),
@free _PER VARCHAR(20),
@Chart VARCHAR(2000),
@azzedinehtmlsql VARCHAR(MAX),
@HTMLTEMP VARCHAR(MAX),
@Title VARCHAR(100),
@Lenovo VARCHAR(100),
@alenzi VARCHAR(MAX)

CREATE TABLE #MOUNTVOL (COL1 VARCHAR(500))

INSERT INTO #MOUNTVOL
EXEC XP_CMDSHELL ‘MOUNTVOL’

DELETE #MOUNTVOL WHERE COL1 NOT LIKE ‘%:%’
DELETE #MOUNTVOL WHERE COL1 LIKE ‘%VOLUME%’
DELETE #MOUNTVOL WHERE COL1 IS NULL
DELETE #MOUNTVOL WHERE COL1 NOT LIKE ‘%:%’
DELETE #MOUNTVOL WHERE COL1 LIKE ‘%MOUNTVOL%’
DELETE #MOUNTVOL WHERE COL1 LIKE ‘%RECYCLE%’

SELECT LTRIM(RTRIM(COL1)) FROM #MOUNTVOL

CREATE TABLE #DRIVES
(
DRIVE VARCHAR(500),
INFO VARCHAR(80)
)

DECLARE CUR CURSOR FOR SELECT LTRIM(RTRIM(COL1)) FROM #MOUNTVOL
OPEN CUR
FETCH NEXT FROM CUR INTO @Lenovo
WHILE @@Fetch _STATUS=0
BEGIN
SET @alenzi = ‘EXEC XP_CMDSHELL »FSUTIL VOLUME DISKFREE ‘ + @Lenovo +»»

FETCH NEXT FROM CUR INTO @Lenovo
END
CLOSE CUR
DEALLOCATE CUR

— SHOW THE EXPECTED OUTPUT
SELECT DRIVE,
SUM(CASE WHEN INFO LIKE ‘TOTAL # OF BYTES : %’ THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), ») AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TOTALSIZE,
SUM(CASE WHEN INFO LIKE ‘TOTAL # OF FREE BYTES : %’ THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), ») AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FREESPACE
INTO #DISKSPACE FROM (
SELECT DRIVE,
INFO
FROM #DRIVES
WHERE INFO LIKE ‘TOTAL # OF %’

GROUP BY DRIVE
ORDER BY DRIVE

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

Источник

Sql error converting varchar to bigint

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

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)

@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

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

in table TMFSCBaseValue PKValidityStartDate_dt data type id varchar

What are the string format of the dates stored in these column and the data type of the @InvoiceDate parameter? Assuming the input parameters are varchar and the column values are in the same format, the query below should provide the desired results. Alternatively, you could cast as date instead of bigint.

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Thanks for your reply.

Dates are stored in the format of 2010-10-06 in column invoice date

I tried above code still i am getting the error like Syntax error converting datetime from character string.

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

Perhaps you have dates that are incorrectly formatted. Run the query below to see if this is the case:

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

If @InvoiceDate is a datetime, then that part is fine (otherwise it is not).

The problem is with the PKValidityStartDate and PKValidityEndDate, since you mention that these are varchar columns. If you convert this to bigint, then the value is assumed to be a number. So if you try that, you will get a conversion error.

Dan’s solution should fix that problem for you, because this first converts the varchar to a datetime. This will work, because you have stored dates in the column (not integer values). Then the value can be converted to bigint.

Having said that: this approach you are using to find out of a date is between a From and To date is nonstandard, not guaranteed to work the way you hope it will and will probably not have good performance. So I suggest you convert your varchar columns to datetime (or date) and change your query to something like this:

Источник

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

dogramone

Hall of Fame

Points: 3216

Jeff Moden

SSC Guru

Points: 1002244