ORA-01830: date format picture ends before converting entire input string
Microsoft SQL Server has the ability to REPLICATE data changes to Oracle. We see more and more customers migrating to SQL Server, then setting up SQL data Replication to move change SQL data back to a legacy Oracle system until the Oracle server is no longer needed. Our customer got this error after setting up SQL 2008 R2 Replication to Oracle.
ORA-01830: date format picture ends before converting entire input string
The Oracle error it turns out was a bit misleading. The problem was not with the Date/Time Data but with the Number data.
–SQL Server Table
CREATE TABLE [Devices](
[ID] [numeric](18, 0)
[DATE_CREATE] [datetime]
[DESCRIPTION] [varchar](100)…–Oracle Table
CREATE TABLE [Devices](
[ID] [NUMBER](9)
[DATE_CREATE] [datetime]
[DESCRIPTION] [varchar](100)…
This mismatch in the Numeric data type caused Oracle OLEDB provider error on the Date/Time data type. Once we changed Oracle to Number(18,0) and matched the SQL Server data types we were able to replicate the data from SQL Server 2008 R2 to Oracle.
