How to Convert nvarchar (including time) into datetime

Question

I have data 20160526094432, and I want to convert into datetime in SQLServer The result will be 2016-05-26 09:44:32

Is there simple way to do that ?

Thanks

By handy to know the name of the database that you are using — Ed Heal, Jun 14 '16 at 02:15
possible duplicate [Convert varchar into datetime in SQL Server](http://stackoverflow.com/questions/1509977/convert-varchar-into-datetime-in-sql-server) — Ren Mark Almojera Cerdan, Jun 14 '16 at 02:18

Alex Kudryashev · Accepted Answer · 2016-06-14T02:40:45.637

1

If you use MS SQL Server 2012 or newer then you can enjoy format function.

select cast(format(20160526094432,'####-##-## ##:##:##') as datetime) [date-time]

If your long number is a string then you have to convert it.

declare @d varchar(20)='20160526094432'
select cast(format(cast(@d as bigint),'####-##-## ##:##:##') as datetime) [date-time]

edited Jun 14 '16 at 02:40

answered Jun 14 '16 at 02:30

Alex Kudryashev

9,120
3
27
36

I am using SQLServer 2014, I want to update my table. update copy_vw_work_in_progress_so_list Set picking_datetime = CAST(FORMAT([picking_date],'####-##-## ##:##:##') as datetime). and error Msg 8116, Level 16, State 1, Line 9 Argument data type nvarchar is invalid for argument 1 of format function. – Henry Jun 14 '16 at 02:44
See my update. `...CAST(FORMAT(cast([picking_date] as bigint),'####-##-## ##:##:##') as datetime)...` – Alex Kudryashev Jun 14 '16 at 02:46

score 0 · Answer 2 · answered Jun 14 '16 at 02:18

Hmmm. I don't think there is a really clean way, but something like this should work:

select (convert(datetime, left(col, 8) as datetime) +
        convert(datetime, convert(time,
                                  stuff(stuff(right(col, 6), 5, 0, ':'), 3, 0, ':')
                                 )
                )
        )

score 0 · Answer 3 · answered Jun 14 '16 at 02:24

0

Maybe you can try in this way, for example Date is the column of your table, 103 is the format of Date you want to convert, google for more details.

CONVERT(datetime, Date, 103)

answered Jun 14 '16 at 02:24

120196

283
6
14

I've already tried but get error Conversion failed when converting date and/or time from character string. – Henry Jun 14 '16 at 02:26

How to Convert nvarchar (including time) into datetime

3 Answers3