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