Format Varchar as datetime2

  • I have a really ugly formatted varchar(255,null) field in a table where the entries look like: '0:2013032614340000:0.000000:0:0'

    I would like to format them as (datetime2(7),null) looking like '2013-03-26 14:34:00.0000000'

    Any tips would be greatly appreciated.

  • You don't need or want the dashes in the date:


    SELECT STUFF(STUFF(STUFF(main_datetime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' ') + '.0000000' AS datetime2_format,
        ugly_column
    FROM (
      VALUES('0:2013032614340000:0.000000:0:0')
    ) AS test_values(ugly_column)
    CROSS APPLY (
      SELECT SUBSTRING(LEFT(ugly_column, 16), 3, 16) AS main_datetime
    ) AS assign_alias_names1

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thank you, that did the trick!

  • This was removed by the editor as SPAM

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

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