The value = 20171030131152 exists in the database as start-time
and 20171030131236 as end-time.
I want to convert it into date time so that I can calculate elapsed time between start-time and end-time.
Below is the code I tried to convert it start-time into datetime
declare @starttime nvarchar(255) = '20171030131152'
select CAST(@starttime as datetime)
Use the datetimefromparts() function
select datetimefromparts( substring(@starttime,1,4), substring(@starttime,5,2), substring(@starttime,7,2), substring(@starttime,9,2), substring(@starttime,11,2), substring(@starttime,13,2), 0 ) ----------------------- 2017-10-30 13:11:52.000
@soldout6000 Just another opinion...
--===== This is just test data and is not a part of the solution. DECLARE @StartDT NVARCHAR(14) = N'20171030131152' ,@EndDT NVARCHAR(14) = N'20171030131236' ; --===== This is just another solution. I've not tested it for performance compared to the other solution. SELECT StartDT = CONVERT(DATETIME,STUFF(STUFF(STUFF(@StartDT,13,0,':'),11,0,':'),9,0,' ')) ,EndDT = CONVERT(DATETIME,STUFF(STUFF(STUFF(@EndDT ,13,0,':'),11,0,':'),9,0,' ')) ;
As a bit of a sidebar, it seems like a real waste to store the original data in an NVARCHAR(anything) column. The data will always be numeric digits and will always be a fixed length of 14 characters. By converting the original column to CHAR(14), you would save 14 bytes from not having to store each character as a 2 byte character and you'd save an extra 2 bytes by not having the overhead of remember how wide the data was.
21 People are following this question.
