question

JCACERES avatar image
JCACERES asked

Convert or cast dates like (Mon Jul 18 19:28:36 EDT 2016) To DateTime

I have a column varchar type with dates like: Fri Mar 3 12:55:17 EST 2017 Thu Jul 27 10:12:07 EDT 2017 Fri Jul 21 12:11:35 EDT 2017 Wed Jan 31 13:15:34 EST 2018 And I would like to return just the date and time something like: 03/03/2017 12:55:17 07/27/2017 10:12:07 07/21/2017 12:11:35 01/31/2018 13:15:34
sqlcast-convertdatetime-formats
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@JCACERES Why are you posting the sample data as image? Also, because the sample data is obviously not in the format that can be converted, please add the sample data for the days less than 10. For example, How is Monday, April 9th going to be spelled out? Is it going to be Mon Apr 09 20:43:41 EDT 2018 or Mon Apr 9 20:43:41 EDT 2018 without zero but with the extra space after the month abbreviation? Please clarify.
1 Like 1 ·
Done, let me know if you need anything else..
0 Likes 0 ·

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
The format of the sting in question does not match any existing date/time format, but kinda resembles format 109, so taking 15 characters starting from position 5 followed by rightmost 5 characters should do the trick, i.e. select convert(datetime, substring(YourValue, 5, 15) + right(YourValue, 5), 109) Converted; The above should do it. Here is the complete sample which includes the original values in question: declare @t table (DateAsString varchar(50)); insert into @t values ('Tue Aug 30 20:43:41 EDT 2016'), ('Fri Dec 15 17:00:00 EST 2017'), ('Fri Aug 11 11:59:25 EDT 2017'), ('Thu Jul 14 09:30:30 EDT 2016'); select convert(datetime, substring(DateAsString, 5, 15) + right(DateAsString, 5), 109) Converted from @t; go -- results: Converted ----------------------- 2016-08-30 20:43:41.000 2017-12-15 17:00:00.000 2017-08-11 11:59:25.000 2016-07-14 09:30:30.000 Hope this helps. Oleg
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you !!!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.