How do I fix date in sql server that does not have a proper Day, Month and Year order

Question

How do I fix date in sql server that does not have a proper day, month and year order?

I'd like to fix this in both sql server and Cognos and apply the standard date format MM/DD/YYYY format. But some records in the Date column (Date of Death column). Some records are on the MM/DD/YYYY format while other records are in YYYY/MM/DD format. Some records doesn't have zeros in the month or day category. For example July 1, 2015, it can either be shown as 0712015 or 7012015 or 2015701. Is there a way to fix this in SQL server? Or should I go to the datawarehouse team and fix the metadata itself?

Answer 1

You have two issues to resolve: fix existing bad data, prevent bad data from being entered moving forward. Is this data coming from upstream somewhere? Looks like it, and i will bet, getting them to fix both will be challenging.

1. If you can have your data source fix it so data is always entered correctly, great.
2. If you can have your data source fix existing data so it is correct, great.

If you are unsuccessful with either, then best thing is to create some kind of batch job that catches or identifies dates in bad format, and then manually fix them. There will be no automated way to fix the data, because as @Quassnoi showed you, distinguishing between 01 and 10, will be difficult.