CODEWITHSUNDEEP
sqlsql-serverdatetime
Arno
Arno

Reputation: 334

SQL convert string to time format

I want to convert string time '141312110' to this: '14:13:12.110'

Actually, I have bigint column has data like '20151110182425000' I want to convert them to datetime format like

'10-11-2015 18:24:25.000'

I divided them to date and time to get date part used this :

Convert(varchar(10), convert(datetime, '20151110'),105)

Result : '10-11-2015'

And needed to get time part like that.

Upvotes: 0

Views: 2539

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can turn your original string into the format "YYYY-MM-DDTHH:MM:SS.SSS'. This should be recognized as a datetime, regardless of internationalization settings.

And you can do this with a bunch of stuff()s:

select cast(stuff(stuff(stuff(stuff(stuff(cast(val as varchar(255), 15, 0, '.'
                                         ), 13, 0, ':'
                                   ), 11, 0, ':'
                             ), 9, 0, 'T'
                       ), 7, 0, '-'
                 ), 5, 0, '-'
             as datetime)

Upvotes: 1

Related Questions