How to do SQL Server date formatting

Question

I am preparing a query on sql server. But I'm stuck with the date format. I want the date format to be at the bottom. If there are letters in it, I don't want it to appear. The ISDATE command only accepts the EN date format. I want to write it as digit2/digit2/digit4 like alt.

SELECT TOP 4  * FROM A AS T WHERE 1 = 1 AND ISNUMERIC(T.Id) = 1


11111111111 FIRSTNAME LASTNAME 19.10.1965
11111111111 FIRSTNAME LASTNAME 15.8.1980
11111111111 FIRSTNAME LASTNAME 12.8.2015
11111111111 FIRSTNAME LASTNAME 3.3.1967

Answer 1

ISDATE is the wrong approach here because it only supports a narrow range of formats and relies on the regional/language settings of the caller, e.g.:

SELECT ISDATE('19.10.1965'), -- 0
       ISDATE('10.19.1965'); -- 1

SET LANGUAGE Türkçe;

SELECT ISDATE('19.10.1965'), -- 1
       ISDATE('10.19.1965'); -- 0

Trying to match a pattern like digit2/digit2/digit4 is also the wrong approach, since it will allow "dates" like 31/02/3456 and 99/99/0000.

Try:

SELECT TRY_CONVERT(date, '19.10.1965', 104);

As a filter:

... WHERE TRY_CONVERT(date, date_column, 104) IS NOT NULL;

Also I would stay away from PARSE/TRY_PARSE/FORMAT as the CLR overhead can be substantial.