0

Oracle DB - ORA-01830 - datefilter

Hi,

I am trying to import data from a Oracle db and need a datefilter to limit the amount of data extracted, for instance last 7 days.

Version: Discovery Hub 19.2.9.64

Everything works fine when I test the SQL in Oracle, but when I put it into TX this error occurs every time:


ORA-01830: date format picture ends before converting entire input string

Details:

ORA-01830: date format picture ends before converting entire input string ...
Module: OraOLEDB
System.Data.OleDb.OleDbException
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at TimeXtender.DataManager.StepTransferSSISExecute.BulkCopySource(Boolean executeIncrementalLoad, DataSource dataSource, VariableResolveObject dynamicResolveObject)
at TimeXtender.DataManager.StepTransferSSISExecute.DoStageDataSource(Boolean isIncrementalAllowed, DataSource dataSource, StepSetup stepSetup, VariableResolveObject dynamicResolveObject)
at TimeXtender.DataManager.StepTransferSSISExecute.DoStage(StepSetup stepSetup, VariableResolveObject dynamicResolveObject)

 

SQL for filter defined as a Projct Variable on the Oracle datasource:

SELECT TO_DATE(CURRENT_DATE-7, 'DD.MM.YYYY') FROM DUAL

 

 

 

Custom data selection rule examples that fails:

[Datefield]>='[Project Variable]'

 

alternatively

 

TO_DATE([Date Column], 'DD.MM.YYYY')>='[Project Variable]'

 

 

In Oracle [Date Column] is defined as data type: DATE

In TX it is translated to: Datetime2(7)

 

How can I debug this?

For instance I can't find any place in TX where I can find the exact SQL sent to Oracle data source when running the execution. So what is happening is kind of black box.

Please advise.

 

5 comments

Please sign in to leave a comment.