How to standardize data format?

Question

I'm having a problem during the data collecting in SQL, apparently, the programmer of one of our production equipments did a update in a MS Access database and changed the date format and it's causing me troubles for lookup specific dates.

Could you recommend how to standardize the dates?

Please have a look on the query results I'm adding here:

Prod_Date
-------------
8/24/2020
8/23/2020
8/22/2020
2020-08-24
2020-08-23
2020-08-22
2020-08-21
2020-08-20
2020-08-19

Answer 1

Provided you are not doing DMY, you can simply use try_convert()

Example

Declare @YourTable Table ([Prod_Date] varchar(50))
Insert Into @YourTable Values 
 ('8/24/2020')
,('8/23/2020')
,('8/22/2020')
,('2020-08-24')
,('2020-08-23')
,('2020-08-22')
,('2020-08-21')
,('2020-08-20')
,('2020-08-19')
 
Select *
      ,AsDate = try_convert(date,[Prod_Date])
 from @YourTable

Returns

Prod_Date   AsDate
8/24/2020   2020-08-24
8/23/2020   2020-08-23
8/22/2020   2020-08-22
2020-08-24  2020-08-24
2020-08-23  2020-08-23
2020-08-22  2020-08-22
2020-08-21  2020-08-21
2020-08-20  2020-08-20
2020-08-19  2020-08-19