Intermittent "Conversion failed..." error

Question

I have several stored procedures. These procedures when called have been fully operational for over 18 months without error. Over the last 3-5 months, occasionally I will receive the error:

Conversion failed when converting date and/or time from character string

Attempts to run the query immediately after initial failure are unsuccessful. If I wait 2-5 minutes, I can run the queries without problem or error.

At first I thought there was an error in the code (CAST or SUBSTRING statements) but these were ruled out.

I pulled each piece of code and ran it individually in the MS SQL Server Console. Each piece of the code ran successfully in the console without error. After this exercise, I was able to run the stored procedure (unaltered) successfully.

I have verified that no one is adding or modifying any data in the database at the time the stored procedure is executed when the error occurs.

Due to the infrequency of the error occurring it is difficult to troubleshoot. After the most recent occurrence my thought was that someone was editing data and that one or more fields were not populated resulting in a divide by zero error however this was not the case. No one was connected to or editing the database at the time.

Any ideas what could cause this issue?

Edit: SP details... Note:

• unable to post entire content of SP as exceeds 30000 char limit
• Selected items in SP with comment tags are omitted, due to duplicate of selected item above.
• Again, just to re-iterate, SP query works most of the time, when the script fails you have to wait 2-5 min to successfully run.

SQL calling SP:

dbo.SP_Report_1 
@YEAR=2016,
@MTH=1,
@CL1_CONTRACT1= '%PROG1%',
@CL2_TELSUP_Contract1='%CL6 TELE%',
@CL2_HW_Contract1='%CL6 PRODTYPE1%',
@CL2_PROD1_Contract1='%PROD1 LCS%',
@CL3_PROD1_Contract1='%CL3 PROD1%'

Tbl Structures:

Tbl Issues
  IS_ISSUE_NO
  IS_ISSUE_TYPE_ID
  IS_RECEIVED_DATETIME   (14 char number as YYYYMMDDhhmmss) not my sw, unable to chg
  IS_RESOLVED_DATETIME   (14 char number as YYYYMMDDhhmmss) not my sw, unable to chg
  IS_COMPANY_ID
  IS_CONTRACT_INSTANCE_ID
  IS_CATEGORY
  REPORTING_METHOD

Tbl Issue_Types
  ISTY_ISSUE_TYPE_ID
  ISTY_ISSUE_TYPE_NAME

Tbl COMPANIES
  CO_COMPANY_ID
  PARENT_ORG0

Tbl CDTBL_COMPANIES_PARENT_ORG0
  CT_CODE
  CT_DATA

Tbl CUSTOMER_CONTRACTS
  CC_CONTRACT_INSTANCE_ID
  CC_CONTRACT_ID

Tbl CONTRACT_MASTER
  CTR_CONTRACT_ID
  CTR_CONTRACT_DESC

Tbl ISSUE_CATEGORIES
  ICG_CATEGORY_ID
  ICG_CATEGORY_NAME

Stored Procedure:

 @YEAR varchar(200),
 @MTH varchar(200),
 @CL1_CONTRACT1 VARCHAR(200),
 @CL2_TELSUP_Contract1 VARCHAR(200),
 @CL2_HW_Contract1 VARCHAR(200),
 @CL2_PROD1_Contract1 VARCHAR(200),
 @CL3_PROD1_Contract1 VARCHAR(200)

 AS
 BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
SET NOCOUNT ON;

-- THIS PROCEDURE RETURNS COUNT OF ISSUES DURING GIVEN PERIOD


SELECT
TOTAL_OPENED_ISSUES,
TOTAL_CLOSED_ISSUES,
CL2_ISSUES,
 --CL1_ISSUES,
 --CL4_ISSUES,
 --CLCAT1_ISSUES,
 --CLCAT2_ISSUES,
 --OTHER_ISSUES,
PRODTYPE2_ISSUES,
 --PRODTYPE1_ISSUES,
 --UNSUPPORTED_PRODTYPE1_ISSUES,
 --CL1_UNSUPPORTED_PRODTYPE1_ISSUES,
 --OTHER_UNSUPPORTED_PRODTYPE1_ISSUES,
 --SYSTEM_ISSUES,
 --OTHER_TYPE_ISSUES,
CL5_CTR_ISSUES, 
 --CL3_PROD1_CTR_ISSUES, 
 --CL2_TELSUP_CTR_ISSUES, 
 --CL2_HW_CTR_ISSUES, 
 --CL2_PROD1_CTR_ISSUES, 
 --CL4_CTR_ISSUES, 
 --UNSUPPORTED_PROD1_ISSUES,
 --OTHER_CTR_ISSUES,
CSRS_BY_EMAIL,
 --CSRS_BY_PHONE,
 --CSRS_BY_VM_CL1_HR,
 --CSRS_BY_VM_D_HR,
 --CSRS_BY_WEB_FRM,
 --CSRS_BY_OTHR,
 --CSRS_BY_SAI,
CL2_HLTHCHK_ISSUES,
 --CL1_HLTHCHK_ISSUES,
 --CL4_HLTHCHK_ISSUES,
 --CLCAT1_HLTHCHK_ISSUES,
 --CLCAT2_HLTHCHK_ISSUES,
 --OTHER_HLTHCHK_ISSUES,
CL5_PRODTYPE2_ISSUES,
 --CL5_PRODTYPE1_ISSUES,
 --CL5_UNSUPPORTED_PRODTYPE1_ISSUES,
 --CL5_SYSTEM_ISSUES,
 --CL5_OTHER_TYPE_ISSUES,
CL6_PRODTYPE2_ISSUES,
 --CL6_PRODTYPE1_ISSUES,
 --CL6_UNSUPPORTED_PRODTYPE1_ISSUES,
 --CL6_SYSTEM_ISSUES,
 --CL6_OTHER_TYPE_ISSUES


FROM 

--ALL TOTAL CSRS OPENED
(SELECT
COUNT(IS_ISSUE_NO) AS TOTAL_OPENED_ISSUES

FROM 
ISSUES LEFT JOIN 
ISSUE_TYPES ON IS_ISSUE_TYPE_ID = ISTY_ISSUE_TYPE_ID 

WHERE
MONTH(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @MTH AND 
YEAR(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @YEAR AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%HEALTH%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%COURTESY%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%INTERNAL%' 
) AS TOTAL_OPENED_CSRs,

--ALL TOTAL CSRS CLOSEDED
(SELECT
COUNT(IS_ISSUE_NO) AS TOTAL_CLOSED_ISSUES

FROM 
ISSUES LEFT JOIN 
ISSUE_TYPES ON IS_ISSUE_TYPE_ID = ISTY_ISSUE_TYPE_ID  

WHERE
IS_RESOLVED_DATETIME <> 0 AND
(
MONTH(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @MTH AND 
YEAR(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @YEAR  AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%HEALTH%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%COURTESY%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%INTERNAL%' 
) AND
(
MONTH(CAST(SUBSTRING (str(IS_RESOLVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RESOLVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RESOLVED_DATETIME,16),3,4) AS DATE)) = @MTH AND 
YEAR(CAST(SUBSTRING (str(IS_RESOLVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RESOLVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RESOLVED_DATETIME,16),3,4) AS DATE)) = @YEAR AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%HEALTH%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%COURTESY%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%INTERNAL%' 
)
) AS TOTAL_CLOSED_CSRs,

-- ORG RELATED CSRS
--ALL CL2 CSRs
(SELECT
COUNT(IS_ISSUE_NO) AS CL2_ISSUES

FROM 
ISSUES LEFT JOIN
COMPANIES ON IS_COMPANY_ID = CO_COMPANY_ID LEFT JOIN
CDTBL_COMPANIES_PARENT_ORG0 ON COMPANIES.PARENT_ORG0 = CDTBL_COMPANIES_PARENT_ORG0.CT_CODE LEFT JOIN 
ISSUE_TYPES ON IS_ISSUE_TYPE_ID = ISTY_ISSUE_TYPE_ID 

WHERE
MONTH(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @MTH AND 
YEAR(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @YEAR AND
CDTBL_COMPANIES_PARENT_ORG0.CT_DATA LIKE '%CL2%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%HEALTH%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%COURTESY%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%INTERNAL%' 
) AS CL2_CSRs, 



-- TYPE RELATED CSRS
--PRODTYPE2 CSRs
(SELECT
COUNT(IS_ISSUE_NO) AS PRODTYPE2_ISSUES

FROM 
ISSUES LEFT JOIN
ISSUE_CATEGORIES ON IS_CATEGORY = ICG_CATEGORY_ID LEFT JOIN 
ISSUE_TYPES ON IS_ISSUE_TYPE_ID = ISTY_ISSUE_TYPE_ID 

WHERE
MONTH(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @MTH AND 
YEAR(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @YEAR AND
ICG_CATEGORY_NAME = 'PRODTYPE2' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%HEALTH%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%COURTESY%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%INTERNAL%' 
) AS PRODTYPE2_CSRs,



-- CONTRACT RELATED CSRS
--CL5 CSRs
(SELECT
COUNT(IS_ISSUE_NO) AS CL5_CTR_ISSUES

FROM 
ISSUES LEFT JOIN
CUSTOMER_CONTRACTS ON IS_CONTRACT_INSTANCE_ID = CC_CONTRACT_INSTANCE_ID LEFT JOIN 
CONTRACT_MASTER ON  CC_CONTRACT_ID = CTR_CONTRACT_ID LEFT JOIN 
ISSUE_TYPES ON IS_ISSUE_TYPE_ID = ISTY_ISSUE_TYPE_ID 

WHERE
MONTH(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @MTH AND 
YEAR(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @YEAR AND
(CTR_CONTRACT_DESC Like @CL5_CONTRACT1
 --OR CTR_CONTRACT_DESC Like @CL1_CONTRACT2
 ) AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%HEALTH%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%COURTESY%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%INTERNAL%' 
) AS CL5_CTR_CSRs,



-- CSRS BY ORIGINATION
--CSRS REPORTED BY EMAIL
(SELECT
COUNT(IS_ISSUE_NO) AS CSRS_BY_EMAIL

FROM 
ISSUES LEFT JOIN 
ISSUE_TYPES ON IS_ISSUE_TYPE_ID = ISTY_ISSUE_TYPE_ID 

WHERE
MONTH(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @MTH AND 
YEAR(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @YEAR AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%HEALTH%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%COURTESY%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%INTERNAL%' AND
ISSUES.REPORTING_METHOD = 2
) AS CSRs_REPORTED_BY_EMAIL,



-- HEALTH CHK CSRS
--ALL CL2 HLTHCHK CSRs
(SELECT
COUNT(IS_ISSUE_NO) AS CL2_HLTHCHK_ISSUES

FROM 
ISSUES LEFT JOIN
COMPANIES ON IS_COMPANY_ID = CO_COMPANY_ID LEFT JOIN
CDTBL_COMPANIES_PARENT_ORG0 ON COMPANIES.PARENT_ORG0 = CDTBL_COMPANIES_PARENT_ORG0.CT_CODE LEFT JOIN 
ISSUE_TYPES ON IS_ISSUE_TYPE_ID = ISTY_ISSUE_TYPE_ID 

WHERE
MONTH(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @MTH AND 
YEAR(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @YEAR AND
CDTBL_COMPANIES_PARENT_ORG0.CT_DATA LIKE '%CL2%' AND
ISTY_ISSUE_TYPE_NAME LIKE '%HEALTH%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%COURTESY%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%INTERNAL%' 
) AS CL2_HLTHCHK_CSRs, 




-- CSR BY CONTRACT (SUB BY TYPE)
-- CL5 RELATED TYPE CSRS
--PRODTYPE2 CSRs
(SELECT
COUNT(IS_ISSUE_NO) AS CL5_PRODTYPE2_ISSUES

FROM 
ISSUES LEFT JOIN
CUSTOMER_CONTRACTS ON IS_CONTRACT_INSTANCE_ID = CC_CONTRACT_INSTANCE_ID LEFT JOIN 
CONTRACT_MASTER ON  CC_CONTRACT_ID = CTR_CONTRACT_ID LEFT JOIN
ISSUE_CATEGORIES ON IS_CATEGORY = ICG_CATEGORY_ID LEFT JOIN 
ISSUE_TYPES ON IS_ISSUE_TYPE_ID = ISTY_ISSUE_TYPE_ID 

WHERE
MONTH(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @MTH AND 
YEAR(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @YEAR AND
(CTR_CONTRACT_DESC Like @CL5_CONTRACT1
 --OR CTR_CONTRACT_DESC Like @CL1_CONTRACT2
 ) AND
ICG_CATEGORY_NAME = 'PRODTYPE2' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%HEALTH%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%COURTESY%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%INTERNAL%' 
) AS CL5_PRODTYPE2_CSRs,



--PRODTYPE2 CSRs
-- CL6 RELATED TYPE CSRS
(SELECT
COUNT(IS_ISSUE_NO) AS CL6_PRODTYPE2_ISSUES

FROM 
ISSUES LEFT JOIN
COMPANIES ON IS_COMPANY_ID = CO_COMPANY_ID LEFT JOIN
CDTBL_COMPANIES_PARENT_ORG0 ON COMPANIES.PARENT_ORG0 = CDTBL_COMPANIES_PARENT_ORG0.CT_CODE LEFT JOIN
ISSUE_CATEGORIES ON IS_CATEGORY = ICG_CATEGORY_ID LEFT JOIN 
ISSUE_TYPES ON IS_ISSUE_TYPE_ID = ISTY_ISSUE_TYPE_ID 

WHERE
MONTH(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @MTH AND 
YEAR(CAST(SUBSTRING (str(IS_RECEIVED_DATETIME,16),7,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),9,2) + CHAR(45) + SUBSTRING (str(IS_RECEIVED_DATETIME,16),3,4) AS DATE)) = @YEAR AND
CDTBL_COMPANIES_PARENT_ORG0.CT_DATA LIKE '%CL2%' AND
ICG_CATEGORY_NAME = 'PRODTYPE2' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%HEALTH%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%COURTESY%' AND
ISTY_ISSUE_TYPE_NAME NOT LIKE '%INTERNAL%' 
) AS CL6_PRODTYPE2_CSRs,

 END

Answer 1

Your intermittent errors probably stem from the explicit CAST conversions that you're performing, combined with the WHERE filters. For any given query, SQL Server will create an execution plan, which you can think of as a roadmap of operations in a given order. The execution plan for any query can often vary in a number of ways, and SQL Server tries to choose the plan that will be the most efficient for every particular scenario. In this choice, lots of factors will influence how the plan is generated.

Once created, the plan is cached, so it can be re-used. If the (verbatim) query isn't re-used any time soon, the data that it depends on is changed significantly or the server is restarted, the cache is cleared and the query plan will be regenerated the next time you run the query. This may very well result in a completely different plan.

Now, for your query, consider the following example table:

Year  DateAsText
----  ----------
...
2015  2015-02-28
2015  2015-02-29
2015  2015-03-01
...
2016  2016-02-28
2016  2016-02-29
2016  2016-03-01

Let's assume we're running this query:

SELECT CAST(DateAsText AS date) AS ProperDate
FROM dbo.demoTable
WHERE [Year]=2016;

Note that we have an invalid date in the table on the "29th" of february 2015, stored as a varchar. But since we're querying 2016, we don't expect to touch that row. However, there are multiple ways to solve this query:

• Retrieve all the rows where Year is 2016, then convert DateAsText to a date.
• Retrieve every row, convert DateAsText to a date, then filter only rows with Year 2016.

The example is simplified, but it still illustrates what's going on.

Your query

Try the following, then go through the results, looking for invalid dates like 31st of november, 0th of january, 29th of february on non-leap-years, negative values (I'm assuming IS_RESOLVED_DATE is numeric), etc.

SELECT DISTINCT STR(IS_RESOLVED_DATETIME, 16)
WHERE ISTY_ISSUE_TYPE_NAME LIKE '%HEALTH%' OR
      ISTY_ISSUE_TYPE_NAME LIKE '%COURTESY%' OR
      ISTY_ISSUE_TYPE_NAME LIKE '%INTERNAL%'
ORDER BY 1;

Solution

Store dates as dates, store numbers as numbers. It's really that simple. Speak to your software people about this. After all, they type all their variables in C#, why so lax in the database?

Short-term

You could replace CAST() with TRY_CAST() if you're on SQL Server 2012 or newer. This returns a NULL value instead of an error whenever the input won't convert.

On older SQL Server versions, there's no easy way to do this - you could try a lot of CASE conditions inside the CAST(), making sure that invalid dates are passed to CASE as NULL values.

CAST((CASE WHEN doesntlookright=1 THEN NULL ELSE datecolumn END) AS date)

Please note that TRY_CAST() (and similar manual workarounds) will produce silent errors in those cases where you actually do have an invalid date in your varchar column.

Based on this question, I also wrote a blog post that may be of interest.