Intermittent “Conversion failed…” error
jsc (imported from SE) 
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?

### Stored procedure details

Note: 

 - Unable to post entire content as exceeds 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
Top Answer
daniel hutmacher (imported from SE) 
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][1] that may be of interest.


  [1]: http://sqlsunday.com/2016/02/17/intermittent-conversion-issues/
comments
transcript

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.