SQL Converting strings to date [closed]

Solution 1:

I'm not sure how well suited your database is to doing this. The data conversion functions in SQL Server are a bit "clumsy", compared to some others.

However, from your database's point of view:

If something is supposed to be a Date, then it should be a date.
It might be "missing" (i.e. nullable) but it can't be anything else.
If it were to be anything else, then it should not be treated as a Date.

Users cannot enter Dates - only the Character Representations of them.
Displaying [numbers and] dates from your application requires formatting on the "way out".
Reading [numbers and] dates into your application requires the reverse, "un-formatting" (and validation, of course) on the "way in".
Data should always be stored in the correct Data Type - "The Right Tool for the Right Job".

Free-text parsing is probably best done in your application, extracting the date value, validating and "un-formatting" it and then storing it.