• rkordonsky 63916 - Thursday, December 14, 2017 9:22 AM

    Luis Cazares - Thursday, December 14, 2017 9:01 AM

    What happens if you run the following query?

    SELECT YAB
    FROM UD_Indiv_Data
    WHERE YAB IS NOT NULL
    AND TRY_CAST( YAB AS DATETIME) IS NULL;

    If you get rows, then those rows are not valid dates.

    Some of Rows contain empty string.

    Technically, that's not an empty string. It might contain a blank character like CHAR(0).
    Here's an example:

    WITH UD_Indiv_Data AS(
      SELECT '' YAB UNION ALL
      SELECT ' ' YAB UNION ALL
      SELECT '20170115' YAB UNION ALL
      SELECT '156431564' YAB UNION ALL
      SELECT '20171301' YAB UNION ALL
      SELECT '20171211 12:23' YAB UNION ALL
      SELECT CHAR(0)
    )
    SELECT YAB,
      ASCII(YAB) AS FirstCharASCII,
      TRY_CAST( YAB AS DATETIME) AS DatetimeYAB
    FROM UD_Indiv_Data
    --WHERE YAB IS NOT NULL
    --AND TRY_CAST( YAB AS DATETIME) IS NULL;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2