# question

## 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)

1 comment

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

0 Likes 0 ·

·

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

```

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

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

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