Convert specific string format to Sql server 2012 datetime

Question

I'm getting date as string from Java output as: Wed Apr 27 09:56:56 CEST 2016

I would like to manually avoid typing "2016-04-2..etc" in my datetime field in my table during debug, and also avoid to create manual method for formating that date.

Instead, I would like to do this on the fly on database. I would like to convert given string format to date. I have tried many examples, but I couldn't solve it. Most close I found was this:

select convert(datetime, 'Wed Apr 27 09:56:56 CEST 2016', 127) from mytable

I need this in SQL Server 2012. Seems due to documentation (https://msdn.microsoft.com/en-us/library/ms187928.aspx), it can parse may formats, but it can't parse this kind of string I noticed.

Anybody knows how to convert this given string to date?

Manoj Naik · Accepted Answer · 2016-04-27 09:14:31Z

3

 select convert(datetime, REPLACE((SUBSTRING('Wed Apr 27 09:56:56 CEST 2016', PATINDEX('%CEST%', 'Wed Apr 27 09:56:56 CEST 2016')+5, 4)
 + ' ' + SUBSTRING('Wed Apr 27 09:56:56 CEST 2016', 5, (PATINDEX('%CEST%', 'Wed Apr 27 09:56:56 CEST 2016')-5))),'CEST',''), 109)

answered Apr 27, 2016 at 9:14

Manoj Naik

3964 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Antony · Accepted Answer · 2016-04-27 09:16:44Z

3

Please try this below query:

 select cast(substring(replace ('Wed Apr 27 09:56:56 CEST 2016','CEST','') ,4,len(replace ('Wed Apr 27 09:56:56 CEST 2016','CEST',''))) as datetime)

edited Apr 27, 2016 at 9:16

answered Apr 27, 2016 at 9:13

Antony

9761 gold badge8 silver badges21 bronze badges

Comments

Pawan Nogariya · Accepted Answer · 2016-04-27 09:17:03Z

2

If the format is fixed, this query should work

SELECT CONVERT(DATETIME,REPLACE(SUBSTRING('Wed Apr 27 09:56:56 CEST 2016',4, LEN('Wed Apr 27 09:56:56 CEST 2016')-3),'CEST','')) from mytable

answered Apr 27, 2016 at 9:17

Pawan Nogariya

9,04614 gold badges61 silver badges137 bronze badges

Collectives™ on Stack Overflow

Convert specific string format to Sql server 2012 datetime

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related