Why does this conversion to date fail on some rows in my table and not other rows when I use an IIF-CodePudding

I have this table and data:

CREATE TABLE dbo.tBadDate
(
  BadDateID    int NOT NULL,
  StartDate    nchar(20) NULL,
  CONSTRAINT [PK_tBadDate] PRIMARY KEY CLUSTERED 
  (
    [BadDateID] ASC
  )
);

INSERT dbo.tBadDate (BadDateID, StartDate) VALUES 
(1, N'1/1/2020            '),
(2, N'Jan 1 2021          '),
(3, N'January 1 2021      '),
(4, N'Ja 1 2021           '),
(5, N'Jan,1,2021          '),
(6, N'2021.1.1            '),
(7, N'8/8/1981            '),
(8, NULL),
(9, N'January First, 2021 ');

This script works:

SELECT StartDate, ISDATE(StartDate) from tBadDate;

This script fails:

SELECT StartDate, IIF(ISDATE(StartDate) = 1 , CONVERT(DATE, 
  startDate), 'Undefined Format') 
  FROM tBadDate

Msg 241, Level 16, State 1
Conversion failed when converting date and/or time from character string.

CodePudding user response：

You could use try_convert with a cross apply

select StartDate,
    Iif(x.v is null,0,1) ValidDate,
    IsNull(Cast(v as varchar(20)),'Undefined Format')
from tBadDate
cross apply (values(Try_Convert(date,StartDate)))x(v)