0

I am taking an Excel file that has dates in different formats but one of these formats is giving me some grief when I try converting it or casting it to a SQL DATETIME. For example:

Thu Oct 16 13:40:19 2014 ,Tue Apr 1 12:20:40 2014, etc.

Essentially the format is ddd MM dd HH:mm:ss yyyy.

I was wondering if there's some kind of date style that would allow me to do a convert in one line. I could write a function for this but I was wondering if there was a more elegant solution. I Googled with no luck.

  • You can do it inline, but it won't be elegant. There is no build-in convert that will handle that format. Is there any way to fix the format in Excel? – Aaron Bertrand Mar 9 '15 at 20:34
  • @AaronBertrand the date column in the file has various different formats and the guy that owns the file claims that he rather not change it because all his historical files are in the same format and he wants to be able to compare these dates in the future. I politely asked him to please add a new column with the dates in ISO8601 format but he claimed that it was too difficult to add more columns. Funny thing is that 15 secs later he offered to add a GUID column to the same file. It's sad and funny at the same time. This is CSV file produced by a Perl script. – Icarus Mar 9 '15 at 20:40
5

This works:

DECLARE @mycol VARCHAR(30) = 'Tue Apr  1 12:20:40 2014'

SELECT CAST(SUBSTRING(@mycol, PATINDEX('% %', @mycol), 100) AS DATETIME)

The day name at the beginning is what prevents it from being able to convert directly.

Instead of declaring the variable, you can just substitute the column containing your dates for @mycol in the script above.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.