Convert to datetime

Question

Post reply

Convert to datetime

Jimmy James-466232

Grasshopper

Points: 12
More actions
November 7, 2007 at 8:41 am

#62581

Hi,
I have a table entries in a column like this:
14:52:17.376 CET Wed Oct 8 2007
14:54:00.737 CET Wed Oct 8 2007
14:08:53.213 CET Wed Oct 9 2007
14:05:51.678 CET Wed Oct 9 2007
14:10:01.668 CET Wed Oct 10 2007
14:19:57.110 CET Wed Oct 10 2007
Is there a way to convert these into datetime datatype? Using ISDATE on them returns 0. The column is varchar(255) now. Thanks for any help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

if you strip out the 'CET WED' from the middle, CAST would work.

Something like

select cast(left(@dte,charindex(' ',@dte)-1)+substring(@dte,charindex(' ',@dte)+8,50) as datetime)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Venkataraman-408293 SSChasing Mays Points: 650 More actions · Answer 2

Try with this...

select cast(right('14:19:57.110 CET Wed Oct 10 2007',11) + ' ' + left('14:19:57.110 CET Wed Oct 10 2007',12) as datetime)

Is this format u r looking for..?:)

Good day

Jimmy James-466232 Grasshopper Points: 12 More actions · Answer 3

Thanks Matt & Venkataraman! I got it working using a combination of both your suggestions!

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

This method seems simple enough:

select

[DateTime] = convert(datetime,stuff(a.DT,13,8,''))

from

-- Test Data

(

select DT= '14:52:17.376 CET Wed Oct 8 2007' union all

select DT= '14:54:00.737 CET Wed Oct 8 2007' union all

select DT= '14:08:53.213 CET Wed Oct 9 2007' union all

select DT= '14:05:51.678 CET Wed Oct 9 2007' union all

select DT= '14:10:01.668 CET Wed Oct 10 2007' union all

select DT= '14:19:57.110 CET Wed Oct 10 2007'

) a

Results:

DateTime

-------------------------

2007-10-08 14:52:17.377

2007-10-08 14:54:00.737

2007-10-09 14:08:53.213

2007-10-09 14:05:51.677

2007-10-10 14:10:01.667

2007-10-10 14:19:57.110

(6 row(s) affected)