March 27, 2017 at 3:42 pm
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.
March 27, 2017 at 3:55 pm
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".
March 28, 2017 at 2:07 pm
Thank you, that did the trick!
March 28, 2017 at 2:23 pm
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