Converting from char(26) to datetime2

Question

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?

Answer 1

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

Answer 2

DECLARE @table_name VARCHAR(100) -- table name  
DECLARE @column_name VARCHAR(100) -- table name
DECLARE @value VARCHAR(100) -- table name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @strSQL NVARCHAR(2000)
DECLARE @msg NVARCHAR(2000)
DECLARE @read_only INT

DECLARE db_cursor CURSOR FOR  
SELECT c.table_name [Table Name], c.column_name [Column Name]
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t
ON c.table_name = t.table_name 
WHERE c.data_type = 'char'
AND c.column_name in ('DATCRE','DATCHG')
AND c.CHARACTER_MAXIMUM_LENGTH = 26 
AND t.table_type = 'BASE TABLE';

  OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @table_name, @column_name

    WHILE @@FETCH_STATUS = 0   
     BEGIN
         SELECT @strSQL = 'UPDATE t
                            SET ' + @column_name + ' = SUBSTRING(tt.' + @column_name + ',1,10) + '' '' + replace(SUBSTRING(tt.' + @column_name + ',12,8),''.'','':'') + SUBSTRING(tt.' + @column_name + ',20,7)
                            FROM vmtest.dbo.' + @table_name + ' t
                            INNER JOIN vmtest.dbo.' + @table_name + ' tt
                            ON t.' + @column_name + ' = tt.' + @column_name + ';'

         SELECT @msg = 'Update Skript = ' + CAST(@strSQL as varchar(max));
         RAISERROR(@msg, 0, 1) WITH NOWAIT;
         EXEC sp_executesql @strSQL;
         SET @strSQL = 'alter table ' + @table_name + ' alter column ' + @column_name + ' datetime2;'
         SELECT @msg = 'Alter Table = ' + CAST(@strSQL as varchar(max));
         RAISERROR(@msg, 0, 1) WITH NOWAIT;
         EXEC sp_executesql @strSQL;
      FETCH NEXT FROM db_cursor INTO @table_name, @column_name
     END   
  CLOSE db_cursor   
DEALLOCATE db_cursor