0

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?

  • do you have EXP_DATE in your My-Table? – Edgar Allan Bayron Dec 14 '17 at 9:40
  • yes i do. Not only this but i only need an working expression. – Michael Dec 14 '17 at 9:53
  • please show the exact error message. we'll check it. – Edgar Allan Bayron Dec 14 '17 at 10:09
  • 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 ":". – Michael Dec 14 '17 at 10:15
2

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

  • @Michael you're welcome, please accept the answer if it works. – McNets Dec 14 '17 at 10:32
  • For anyone who has the same problem here is a script to convert the columns. – Michael Dec 14 '17 at 10:33
  • @Michael there is a check below up-down votes counter. click it. – McNets Dec 14 '17 at 10:34
0
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.