x

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
more ▼

asked Apr 11 at 02:58 PM in Default

avatar image

JCACERES
71 2 5

@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.

Apr 11 at 05:12 PM Oleg

Done, let me know if you need anything else..

Apr 11 at 05:20 PM JCACERES
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Apr 11 at 05:28 PM

avatar image

Oleg
20.6k 3 7 29

Thank you !!!

Apr 11 at 05:32 PM JCACERES
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1208
x7
x3

asked: Apr 11 at 02:58 PM

Seen: 27 times

Last Updated: Apr 11 at 05:32 PM

Copyright 2018 Redgate Software. Privacy Policy