I have a table in SQL database in which there is an NVARCHAR(MAX) column. In it, the data is stored as 'Datetime1&Datetime2&Datetime3&Datetime4...'(& separated dates. I know this is not correct way to store data but I have to support an older system).
Now the issue is that this table is used in multiple sites and hence the data coming in this column could come in different formats. Means, for one site, the datetime could be in the format '22-06-2015 13:00:00' and for some other site, the datetime could be in the format '2015-06-22 13:00:00' or '2015-22-06 13:00:00'.
I have to break the data from this column and store individually each value as a datetime in a new row in another table. This has to be datetime only as I need to do some date operations like comparison, Datediff, Dateadd etc.
So, in my Stored Procedure, if I select value as
convert(datetime,[date],121), then it gives error if the string is in any other format than 121(yyyy-mm-dd hh:mm:ss.mmm).
If I check whether the string is starting from a year, then I am sure it would be in the format
YYYY-MM-DD, but if its starting from a month or day, then I can differentiate between the two in case of dates less than or equal to 12.
So how can I address this problem that this datetime string could be converted to actual datetime without any issue of a particular format or system format?