How to insert date from SQL Server by using LinkedServer to Oracle in TimeStamp(6) format

2
votes

I have some records i've to migrate to Oracle server from SQL Server by using LinkedServer.

The date in SQL Server: 2014-12-31 11:16:39.000

Date should be converted to this format for Oracle: timestamp(6).

For this operation I've tried some scripts. These scripts work succesfully outside of insert script for Oracle Migrating. But any result of these isn't relevant for Oracle date type.

Scripts executed : 

CONVERT(varchar(24), BEGIN_DATETIME, 121) AS BEGIN_DATE`

tried 20, 21, 120

CURRENT_TIMESTAMP AS BEGIN_DATE

CAST(BEGIN_DATETIME as timestamp)

TO_TIMESTAMP(BEGIN_DATE, 'YYYY-MM-DD HH24:MI:SS')

returns to_timestamp' is not a recognized built-in function name

And the scripts return an error:

The OLE DB provider "OraOLEDB.Oracle" for linked server "LNK_DEV" supplied invalid metadata for column "BEGIN_DATE". The data type is not supported.

1
sql-serveroracledatetimestamplinked-server

1 Answers

0
votes

I solved my problem without formating or changing date value in SQL Server while migrating. I tried LinkedServer script in diffrent type.

Script Type-1 for LinkedServer

INSERT INTO 
    [LNK_DEV]..[TEST].[DIAG_TABLE]
SELECT
    [BEGIN_DATE] = CONVERT(varchar(24), BEGIN_DATE, 121) AS BEGIN_DATE
FROM 
    TEST.DIAG_TABLE_SQLSERVER

This script returns error : The OLE DB provider "OraOLEDB.Oracle" for linked server "LNK_DEV" supplied invalid metadata for column "BEGIN_DATE". The data type is not supported.

Script Type-2 for LinkedServer

INSERT INTO
    OPENQUERY([LNK_DEV], 'SELECT BEGIN_DATE FROM TEST.DIAG_TABLE') 
SELECT 
    BEGIN_DATE
FROM 
    TEST.DIAG_TABLE_SQLSERVER

This query has been completed successfully and the records have been moved to Oracle Server correctly.