I have a CSV with what I think is an unusual date format:

Mon Nov 14 2016 11:31:26 GMT+0200 (South Africa Standard Time)

I would like to convert that a DATETIME2 (or other date format) in SQL Server 2016.

Is there an easy way to do this or would I have to do some kind of string manipulation first? I find that if I can shorten the string to SELECT CONVERT(datetime2, 'Nov 14 2016 17:12:18') then I get a result.

  • Does TIME ZONE matter? Can you modify the .csv file? – McNets Nov 15 '17 at 8:32
  • Timezone doesn't matter, but I can't modify the CSV file. I basically replaced the GMT.*, and the leading Day in the SQL – Zach Smith Nov 15 '17 at 8:52
  • Then IMHO you should deal with CONVERT. dbfiddle.uk/… – McNets Nov 15 '17 at 8:53
  • Thank you - I did something similar to that, although I did it in two stages: 1) i created a suitable string and then made that a sub-query and selected the conversion. Do you know if creating unnecessary sub-queries is a bad performance hit? – Zach Smith Nov 15 '17 at 9:36
  • 3
    The data example you have provide looks like a standard date with a timezone offset. I am not sure if you have seen this article: docs.microsoft.com/en-us/sql/t-sql/functions/… but this may help you convert the date to something more useable. – Chris Lumnah Nov 15 '17 at 13:25

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.