SQL convert string to time format

Question

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.

What DBMS are you using (SQL Server, MySQL, Oracle, something else)? And what version of the DBMS are you using? The answer will be very different depending on that. — Siyual
– Siyual, Commented Nov 10, 2015 at 16:43
Well it's the same as getting the date part, you convert to varchar and parse the string. What part are you stuck on? — Tab Alleman
– Tab Alleman, Commented Nov 10, 2015 at 16:44
Added sql-server tag based on the usage of convert() and datetime — user330315
– user330315, Commented Nov 10, 2015 at 19:37
@tab alleman time part not getting with the same way cause convertting datetime, time format not mach. — Arno
– Arno, Commented Nov 11, 2015 at 6:10

Gordon Linoff · Accepted Answer · 2015-11-10 16:59:01Z

1

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)

answered Nov 10, 2015 at 16:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arno Over a year ago

Yes i also did this but i need dd-mm-YYYY hh:mm:ss, and can't do that using the stuff or i did not discover the way of doing this with stuff

Collectives™ on Stack Overflow

SQL convert string to time format

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related