How to convert “Thu Jun 11 00:49:35 IST 2015” to “YYYY-MM-DD hh:mm:ss” in SQL Server?

Question

How to convert “Thu Jun 11 00:49:35 IST 2015” to “YYYY-MM-DD hh:mm:ss” in SQL Server?

I tried to convert and casting but it's throwing an error

SELECT [Datetime] = CAST(CAST('Thu, 13 Jul 2017 18:46:50 IST' AS VARCHAR) AS datetime) i tried it — Tarun Yaduvanshi, Jul 16 '17 at 08:50
Not cast, https://www.google.co.il/search?q=mysql+date+format&ie=UTF-8&oe=UTF-8&hl=en-il&client=safari&gfe_rd=cr&ei=6ShrWezyAsb68AeCsY_IDg — EvgenyKolyakov, Jul 16 '17 at 08:51
I also tried convert https://stackoverflow.com/questions/74385/how-to-convert-datetime-to-varchar — Tarun Yaduvanshi, Jul 16 '17 at 08:53
You're kind of missing the point. You at least need to specify the source and target data types. Why do you need a date in a certain format? Are you certain you understand how date data types work? — Nick.Mc, Jul 16 '17 at 12:52

score 0 · Answer 1 · answered Jul 16 '17 at 12:10

A few things to note about this before I posit a solution:

The source of this information should be modified, if at all possible, to conform to some ISO-standard date.
SQL Server pre-2016 doesn't handle time zone names well, so I'm going to presume that all of your strings contain "IST". If they don't, you'll have to adjust accordingly by searching for them and creating some kind of switch case to modify the resulting datetimeoffset.
I have no idea whether your day representation is two-digit or variable-digit. I've assumed that it's variable, but you can probably simplify this answer with fixed-string parsing if it's two-digit.
Since you have a timezone, the target representation of YYYY-MM-DD hh:mm:ss is ambiguous. I've converted it here to UTC.

If your string is in a table called Dates with column HorribleString, then:

select
  convert(varchar(100), convert(datetime2, convert(datetimeoffset, 
    substring(HorribleString, len(HorribleString) - 3, 4) -- Year
    + substring(HorribleString, 4, len(HorribleString) - 12) -- len(Thu ) + len( IST 2015) = 12
    + case substring(HorribleString, len(HorribleString) - 7, 3) -- Timezone
        when 'IST' then ' +05:30'
        else ''
      end
  , 109), 1), 20)
from
  Dates

How to convert “Thu Jun 11 00:49:35 IST 2015” to “YYYY-MM-DD hh:mm:ss” in SQL Server?

1 Answers1