Has anyone had issue with un-representable Date Time ? [Solved]

Answered

Guillaume Brochet

• February 14, 2020 09:48
• Edited

Hi,

Collect jobs fails for some table due to un-representable data time.

Source is Oracle with Date in UTC format, source target is ADM sdbxxx table.

 

Any idea what how i can correct this ?

 

Year, Month, and Day parameters describe an un-representable DateTime. Source connection Test Failed. External Process Exception in ExecutePackage Class in D:\BOA\DSP\Web\UserArea\00be69ce-b45a-4704-ae2f-c3378e71baa3\Processes\cranport\cranportplugin.dll [...]

at System.Data.Odbc.OdbcDataReader.internalGetDateTime(Int32 i)   at System.Data.Odbc.CNativeBuffer.MarshalToManaged(Int32 offset, SQL_C sqlctype, Int32 cb)   at System.Data.ProviderBase.DbBuffer.ReadDateTime(Int32 offset)   at System.DateTime.DateToTicks(Int32 year, Int32 month, Int32 day)

 

Best regards

 

1

• 

  Kurt Vandergriend

  • January 24, 2020 16:45
  • Edited

  A Google search reveals this error is related to incorrect conversion of a Date/Time field.  For example, it could be expecting year, month, day to be in a specific order, or the date may not even exist (such as Feb 30).  Two things to check:

  1) In Collect > Administrative > Connection Types > Source, so to the Source Connection Type you are using, and click on "Data Type Conversions."  Look for Date/Time Data Types, and validate the "New Data Type Format" is correct (it is typically NVARCHAR).

  2) Check the raw source data and make sure there are no bad date values (such as Feb 30).  You may need your Oracle DBA to help with this.

  1
• 

  Jake Cohen

  • January 24, 2020 16:48

  Hi Guillaume,

  Is this an issue that the column data type mapping isn't working?  This can happen sometimes with date/time datatypes in Oracle.  The easiest way to fix it as a one off is to understand the datatype in Oracle and determine what you want this to go to in SQL Server (sdbxxx), go to the vertical of the table in Collect and go to the General tab and click on 'View Design'.

  From here you can change the column level definitions and update the SQL statements.  Then delete the table from the SDB, rebuild the package and re-run the extract.

  If this doesn't work can you provide some screenshots?

  1
• 

  Guillaume Brochet

  • January 28, 2020 15:59

  Thanks Kurt & Jake for your fast input on possible solutions.

  Kurt

  1) I have chekced the "data type conversion" and it seems ok

  2) I'll try to have a look at raw source, but it means check a lot of data

  Jake

  I have checkde in "View design", and the column data type mapping wasn't applied ... i've changed it manually, but did not resolve the issue.

   

   

   

  0
• 

  Jake Cohen

  • January 28, 2020 17:26

  Can you post screenshots so we can see what you are looking at?

  0
• 

  Guillaume Brochet

  • January 29, 2020 13:11

  Jake,

  I could download the content of my source by removing the field that created the error.

  It looks like in my source (Oracle) there are two type of date

   - type:UTC length:4  : is correctly imported

  - type : dte length : 4 : is where the error happened.

   

  In the data type conversion, i have only a reference to "DATE", so i don't know how to change the convesion for type "dte"

   

  

  0
• 

  Guillaume Brochet

  • February 14, 2020 09:48

  Thanks a lot to Syniti support : 

  Apply a TO_CHAR("#COLUMNNAME#",'DDMMYYYY') "#COLUMNNAME#" on conversion from date.

  Resolved the issue.

   

  0

Please sign in to leave a comment.