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
@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.
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
18 People are following this question.
