Post reply

Convert to datetime

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

  • November 7, 2007 at 8:56 am

    #749136

    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?

  • November 7, 2007 at 9:04 am

    #749137

    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

  • November 8, 2007 at 3:17 am

    #749385

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

  • November 8, 2007 at 3:15 pm

    #749664

    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)

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

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