Convert mixed datetime to a unified format in SQL

The general advice is to make sure your schema uses a Date Typed column to store the information, not a character based column. That way your application is forced to enter the data in the correct format.

Date and time data types and functions (Transact-SQL)

Rather than manipulating the data on every read, if the data is stored in a standard format, queries against that data will be far more efficient even if the data is not indexed, but especially if it is.

• The specifics to this with regard to Date and Time values are important to understand but outside of the scope of this post. It makes sense though to process and sanitise the data inputs once during the write operation instead of for every read operation, using appropriate data types is the first step in designing an efficient SQL data schema.

But if we have to, you can use CAST, it might be important to filter out the values that will cause the cast to raise an exception:

WITH StringDates as (
    SELECT '1/1/2020' as [Date]
    UNION 
    SELECT '2022-01-01'
    UNION 
    SELECT 'Not valid'
)
SELECT [Date], ISDATE([Date]) as IsDate, Cast([Date] as Date) as TypedDate
FROM StringDates
WHERE ISDate([Date]) = 1

You can use the same logic to do this conditionally in a set:

WITH StringDates as (
    SELECT '1/1/2020' as [Date]
    UNION 
    SELECT '2022-01-01'
    UNION 
    SELECT 'Not valid'
)
SELECT [Date]
     , ISDATE([Date]) AS IsDate
     , CASE WHEN ISDATE([Date]) = 1 THEN Cast([Date] as Date) END AS TypedDate
FROM StringDates

A possible approach is the TRY_CONVERT function with an appropriate date and time style. Note, that the result from this conversion is usually ambiguous (what date is 05/06/2020 for example), so you need to define the style precedence.

Sample data:

SELECT *
INTO Data
FROM (VALUES
   ('1/1/2020'),
   ('2022-01-01'),
   ('13/13/2020'),
   ('5/6/2020')
) v ([Date])

Statement:

SELECT 
   DateAsText = d.Date, 
   DateAsDate = (
      SELECT TOP 1 TRY_CONVERT(date, d.Date, v.Style) 
      FROM (VALUES (1, 103), (2, 23)) v (Precedence, Style)
      WHERE TRY_CONVERT(date, d.Date, v.Style) IS NOT NULL
      ORDER BY v.Precedence
   )  
FROM Data d

Result: