-1

I'm trying to convert a datetimestamp that is stored as char(20) to datetime.

So, convert a char(20) 202107011008330870 TO a datetime field with this format 2021-07-01 10:14:56.870

ana
  • 7
  • 1
  • 1
    Please explain the mapping; it is not obvious. I get the date portion but not the time portion. Where does the 14:56 come from? – Gordon Linoff Jul 01 '21 at 14:24
  • Its not easy converting as its string see ('SQL Server (2005, 2000, 7.0) does not have any flexible, or even non-flexible, way of taking.....') what I am telling in the answer -https://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion – Ajay2707 Jul 01 '21 at 14:25
  • 1
    If you *do* get the time `10:14:56.870` from `'1008330870'` then your value is most certainly *not* in the format `yyyymmddhhmmssffff` as you state in the title. That format would give you `10:08:33.0870` as the time portion from the string you have provided. – Thom A Jul 01 '21 at 14:28
  • @Ajay2707 why link to documentation for SQL Server 2005? It's been completely unsupported for 5~ years now. If the OP *is* using such an outdated version they haven't told us in the question itself. – Thom A Jul 01 '21 at 14:33

1 Answers1

1

One method would be to inject the necessary characters into the string, and then CONVERT:

SELECT TRY_CONVERT(datetime2(4),STUFF(STUFF(STUFF(STUFF(V.YourString,15,0,'.'),13,0,':'),11,0,':'),9,0,' '))
FROM (VALUES('202107011008330870'))V(YourString);
Thom A
  • 88,727
  • 11
  • 45
  • 75