4 Replies Latest reply on Jan 14, 2016 7:43 AM by scuser20532

    SQL Server Date format issue

    New Member

      Hello everyone

       

      I am facing an issue with the date format in SQL Server  "12/11/2014 13:21:21.000000000"- the error message is - Microsoft SQL Server Native Client 11.0: Invalid character value for cast specification


      If I remove the trailing 0s and just leave 3 of them that make the length as 23 Chars instead of 29 Chars that Informatica defines it as, then I am able to insert this row but as it is I get the error specified above.


      Please let me know if any of you have seen this before and if you know a work-around.


      My Config property has the date format defined as MM/DD/YYYY HI24:MI:SS US


      Thanks a lot for the support

        • 1. Re: SQL Server Date format issue
          Guru

          Hi Veerabhadra,

           

          What is the target in this mapping and the corresponding target column data type and precision?

           

          Regards,

          Sujata

          • 2. Re: SQL Server Date format issue
            New Member

            Hi Sujata

             

            Target is SQL Server DB and the Data type is Date.

             

            The issue is Informatica defines the DateTime field with 29 Char length whereas SQL Server DB has it as 23 Chars. But the strange thing is I have several other workflows that do not face this issue with any date fields. It only happens with this particular workflow.

             

            Severity    Timestamp    Node    Thread    Message Code    Message

            ERROR    1/28/2015 10:09:30 AM    NODE01_PROD    WRITER_1_*_1    WRT_8229    Database errors occurred:

            Microsoft SQL Server Native Client 11.0:    Invalid character value for cast specification

            SQL State: 22018    Native Error: 0

             

            Microsoft SQL Server Native Client 11.0:    Invalid character value for cast specification

            SQL State: 22018    Native Error: 0

             

            Microsoft SQL Server Native Client 11.0:    Invalid character value for cast specification

            SQL State: 22018    Native Error: 0

             

             

            Database driver error...

            Function Name : Execute Multiple

            SQL Stmt : INSERT INTO xxx (Column list)  VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

            • 3. Re: SQL Server Date format issue
              New Member

              Try by enabling the "Pre 85 Timestamp Compatibility" option in the Session Properties -> Config Object

              • 4. Re: SQL Server Date format issue
                New Member

                This has been resolved and thanks for those that attempted to help me.

                 

                The issue was not with the dateformat nor SQL Server. It was with the BULK vs NORMAL property setting in the session - Target Load Type property. Once it is changed back to NORMAL, the problem was resolved.