[SOLVED] Azure Synapses, CSV, Convert VARCHAR to DATE, A few rows worked and others failed

Azure Synapses, CSV, Convert VARCHAR to DATE, A few rows worked and others failed

Please help me to convert a column from VARCHAR to DATE of a csv file. I am baffled by the result that a few rows failed, while they looked normal when selected in their VARCHAR form.

I think this is likely due to the source CSV file containing hidden characters, however I opened it with VSCode and notepad, but did not spot any. Please help on converting this column. I also tried ingesting the [CarryOverDate] column as Date, but failed due to conversion error.

Thank you in advance.

Edit summary: edited grammar and wording.

SELECT
[main].[CarryOverDate] AS DateinText,
TRY_CONVERT(DATETIME2, LTRIM(RTRIM([main].[CarryOverDate])), 21) AS DateinDate
FROM
OPENROWSET(
    BULK (
        'test/test123.csv'
    ),
    DATA_SOURCE = 'ds_test',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) WITH (
    [CarryOverDate] VARCHAR (20)
) AS [main]

Solution

I think tis is because June doesn't have 31 days. The input that is given to the system is not a valid date.

Please see if the NULL's you are getting is for valid dates.