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?

  • 4
    You can't. This is impossible as you have no way of knowing whether 06-07 is 6 July or 7 June – Martin Smith Jun 10 '16 at 12:02
  • As @Martin alluded to, you'll need to know the localization characteristics for each site to successfully translate the dates. Are the dates programmatically generated or entered as text by a human? – Max Vernon Jun 10 '16 at 12:21
  • Thanks for the replies. The data comes from an excel file which is imported in SQL table through SSIS. – Hemant Sisodia Jun 10 '16 at 12:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.