question

soldout6000 avatar image
soldout6000 asked

How to convert nvarchar into datetime?

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)

sql-server-2008t-sqlconversion
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

20171030131152 reads as 10-30-2017 13:11:52

0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

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


10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered

@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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.