Sql-server – Converting from char(26) to datetime2

datetimereplacesql-server-2016type conversion

In course of a change I migrated a database from Oracle 12.1 to MSSQL 2016.
Since the two databases handle date differently, I now need to move the fields from char(26) to datetime2.
Output format is e.g. 2011-05-30-13.06.56.824000 What I need is Type 21: 2016-04-01 10:20:56.8950000.
I already tried using 

SELECT CONVERT(
           DATETIME2
          ,LEFT (EXP_DATE ,10) + "" +
           REPLACE (SUBSTRING(EXP_DATE ,12 ,8) ,"." ,":") + RIGHT(EXP_DATE ,7)
          ,21
       ) AS DateFromString
FROM   My-TABLE;

but this does not work (wrong column name):

Message 207, level 16, status 1, line 1 Invalid column name " ".
Message 207, level 16, status 1, line 1 Invalid column name ".".
Message 207, level 16, status 1, line 1 Invalid column name ":".

Can someone help me?

Best Answer

You should change double-quotes by single-quotes.

'.', ':'

CREATE TABLE MyTable(EXP_DATE varchar(26));
INSERT INTO MyTable (EXP_DATE ) VALUES ('2011-05-30-13.06.56.824000');
GO

1 rows affected

SELECT CONVERT(datetime2,
               CONCAT(LEFT (EXP_DATE, 10), ' ',
                      REPLACE (SUBSTRING (EXP_DATE, 12, 8), '.', ':'),
                      RIGHT (EXP_DATE, 7)),
               21)
FROM MyTable;
GO

| (No column name)    |
| :------------------ |
| 30/05/2011 13:06:56 |

dbfiddle here

Related Question