Hardik Parmar Hardik Parmar - 6 months ago 13
SQL Question

How to convert String to Datetime including time part in SQL

I want to convert below string to DateTime in SQL.

20140601152943767


I know
convert(date,'20140601152943767')
this but I want time part also.

Above function only returns me Date part.

Thanks in advance.

Answer

I would use following solution:

SELECT CONVERT(DATETIME, STUFF(STUFF(STUFF(STUFF('20140601152943767', 9, 0, ' '), 12, 0, ':') , 15, 0, ':'), 18, 0, '.'))

Note #0: All those STUFF calls will convert source strings from 20140601152943767 to 20140601 15:29:43.767.

Note #1: SELECT STUFF('abcef', 4, 1, 'DDD') will replace substring starting from index 4 with a length of 1 char (e) with DDD -> abcDDDf

Note #2: SELECT STUFF('abcef', 4, 0, 'DDD') returns abcDDDef

Comments