Invalid length parameter passed to the SUBSTRING function - date format related

Vote count:

0

The query below returns the following error: "Invalid length parameter passed to the LEFT or SUBSTRING function."

When it runs correctly, it returns the case_id and the data between the 8th and 9th tab characters in the ct.case_text column.

The parameters for the SUBSTRING function in the query do evaluate to positive integers. b.received is a DATETIME column

SELECT TOP 1000 b.case_id,
SUBSTRING(ct.case_text, CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(
          CHAR(9), ct.case_text, CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(CHAR(9), ct.case_text, 
              CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(CHAR(9), ct.
                    case_text) + 1) + 1) + 1) + 1) + 1) + 1) + 1) + 1, CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(
              CHAR(9), ct.case_text, CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(CHAR(9), ct.case_text, 
                  CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(CHAR(9), ct.case_text) + 1) + 1) + 1) + 1) + 1) + 1) + 1
      ) + 1) - CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(CHAR(9), ct.
          case_text, CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(CHAR(9), 
                ct.case_text, CHARINDEX(CHAR(9), ct.case_text, CHARINDEX(CHAR(9), ct.case_text) + 1) + 1) + 1) 
            + 1) + 1) + 1) + 1)) 
FROM case_id b
INNER JOIN case_text ct ON b.company_id = ct.company_id
  AND b.case_id = ct.case_id
  AND ct.case_text_seq = 1
WHERE NOT EXISTS (
    SELECT *
    FROM case_text ct1
    WHERE ct1.case_id = ct.case_id
      AND ct1.case_text_seq = 3
    )
  AND b.received BETWEEN CAST('2014-12-01' AS DATE) AND CAST('2015-01-29' AS DATE)
  AND b.b13_code IS NOT NULL
  AND ct.description IS NULL 
  AND b.case_id IN (4222729, 2840639, 3359716, 3365915)

If I comment out the line below, the error disappears.

AND b.received BETWEEN CAST('2014-12-01' AS DATE) AND CAST('2015-01-29' AS DATE)

Does anyone know why commenting out that line will cause this to run correctly? That condition is necessary to identify the full set of data; the case_id filter is used currently for an initial test.

asked 3 mins ago

Charlie Carwile

50

Invalid length parameter passed to the SUBSTRING function - date format related