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

Answered

Comments

6 comments

  • Avatar
    Kurt Vandergriend

    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
    Comment actions Permalink
  • Avatar
    Jake Cohen

    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
    Comment actions Permalink
  • Avatar
    Guillaume Brochet

    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
    Comment actions Permalink
  • Avatar
    Jake Cohen

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

    0
    Comment actions Permalink
  • Avatar
    Guillaume Brochet

    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
    Comment actions Permalink
  • Avatar
    Guillaume Brochet

    Thanks a lot to Syniti support : 

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

    Resolved the issue.

     

    0
    Comment actions Permalink

Please sign in to leave a comment.