CODEWITHSUNDEEP
sqlparametersexcel-2010truncation
Seva
Seva

Reputation: 57

SQL: Parameters from excel generate error: String data, right truncation

After adding parameter2 to the query, I got an error message: SQL Server Error “String Data, Right Truncation”.

The parameters are entered by the user in Excel 2010 spreadsheet and used in the SQL statement to update the Report.

Example:

Fiscal year (parameter1): 2014, GL Date (parameter2) yyyymmdd: 20140228

The error is generated by: "GLPOST.JRNLDATE<=?" which is parameter2 

WHERE GLAMF.ACCTID = GLPOST.ACCTID AND GLACGRP.ACCTGRPCOD = GLAMF.ACCTGRPCOD AND 
GLPOST.DRILLDWNLK = BKTRAND.DDLINK AND GLACGRP.SORTCODE='1000' AND   
GLPOST.FISCALYR>=? AND GLPOST.JRNLDATE<=?

In the database, GLPOST.FISCALYR is (char(4), not null) and works fine.
GLPOST.JRNLDATE is (decimal(9,0), not null) causes an error. If I enter a hardcoded value such as: 20140228, 20131231, etc... it works but it doesn't seem to pick up the value from the cell.

I did try different formattings but with no success, any idea? thanks!!

Upvotes: 0

Views: 1726

Answers (1)

attila
attila

Reputation: 2229

Maybe try casting the parameter like this:

GLPOST.FISCALYR>=? AND GLPOST.JRNLDATE<=cast(? as decimal(9,0))

Upvotes: 1

Related Questions