How to convert a timestamp string into datetime in sql server?-CodePudding

I was doing some data-check for 1-2 months old data in my sql server and a question rised in my mind.

I made a string 926084711140 and 20210926084711140 which I am considering to be a date-time stamp.

I thought of converting into actual datetime value. So I tried this:

'2021-' SUBSTRING(CAST(th.edit_timestamp as varchar(50)),1,1) '-' SUBSTRING(CAST(th.edit_timestamp as varchar(50)),2,2)

And this returned a value 2021-09-.2 I am unable to understand this output.

I thought of doing substring but so far it failed.

Can anyone help regarding this? Any help would be appreciated.

Thanks!

CodePudding user response：

The first thing I'm going to tell you is: don't use this format. You have to fix the data input, don't try to fix the data output

But if there is no other way ... I'm going to assume that your format is aaaa-mm-ddThh:mi:ss.mmm

We can do something like this

select convert(datetime,'2021-09-26T08:47:11.140',126)

OUTPUT: 2021-09-26 08:47:11.140

declare @date varchar(50) = '20210926084711140'

select convert(datetime,substring(@date,1,4)   '-'   substring(@date,5,2)   '-' 
  substring(@date,7,2)   'T'   substring(@date,9,2)   ':'   
substring(@date,11,2)   ':'   substring(@date,13,2)   '.'   
substring(@date,15,3),126)

one more time ... this is horrible.

try to fix your data source and not work with this.

Good luck

CodePudding user response：

As others have stated - you should fix your data model, but here is an alternative method:

Select cast(stuff(stuff(stuff(stuff(@date, 15, 0, '.'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ') as datetime)

And if you really have the first date format without the year you can do this:

Select cast(stuff(stuff(stuff(stuff(concat('2021', right(concat('0', @date), 13)), 15, 0, '.'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ') as datetime)