Date conversion form string dd/mm/yy is failing-CodePudding

I have a SQL Server date in the format of dd/mm/yy in a varchar column. I am trying to convert this string to date but getting

Msg 241, Conversion failed when converting date/time from character string.

I have tried CAST(), TRY_CAST(), TRY_CONVERT(), CONVERT, PARSE(), TRY_PRASE(). None of them is working.

This is my code:

DECLARE @d varchar(50) = '13/09/22'

SELECT TRY_CONVERT(datetime, @d, 103) -- I have tried different formats to no avail.

Also I have tried casting as below

SELECT TRY_CAST(@d AS datetime) -- Tried datetime2 as well, but all failing.

If I change the day part to < 12, the conversions work. Which means SQL Server is somehow interpreting this as mm/dd/yy?

Thanks

CodePudding user response：

Actually CONVERT() works if you use the correct mask:

SELECT CONVERT(datetime, '13/09/22', 3);  -- 2022-09-13 00:00:00.000

The problem is that mask 103 assumes a 4 digit year, not a 2 digit year. For a 2 digit year date in dd/mm/yy format, use mask 3.