Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Date conversion error between SQL Server and Postgres

  1. #1
    Join Date
    Jul 2014
    Posts
    2

    Default Date conversion error between SQL Server and Postgres

    I have a simple transformation that is just copying data in a single table from an MSSQL server to a Postgres server. I'm getting the following error when running the transformation:

    Code:
    2014/12/08 13:45:07 - write to [requests].0 - Batch entry 0 INSERT INTO requests (id, "key", created_at, updated_at, folder_id, pdf, coverage_id, account_id, plan_id, effective_on, effective_through, deleted_at, active_review_id, starred, star_note, starrer_id, starred_at, from_central) VALUES ( '61',  'XXXXXX',  '2013-07-19 16:00:12.840000 -04:00:00',  '2013-08-23 14:21:43.987000 -04:00:00',  '6',  'request.pdf',  '2',  '1',  '1',  NULL,  NULL,  NULL,  '25',  '0',  NULL,  NULL,  NULL,  '0') was aborted.  Call getNextException to see the cause.
    2014/12/08 13:45:07 - write to [requests].0 - ERROR: column "effective_on" is of type date but expression is of type character varying
    2014/12/08 13:45:07 - write to [requests].0 -   Hint: You will need to rewrite or cast the expression.
    The column is defined as a DATE type on both the MSSQL and Postgres servers, so I'm not sure why there would be a problem.

    I've tried explicitly casting the column to a date in the select statement within my table input, that had no effect. Then I tried casting it as a datetime, but got an out of range error. I also tried using a select values step to convert it to date, but that gave me a different error saying "effective_on String(10) : couldn't convert string [2013-08-27] to a date using format [yyyy/MM/dd HH:mm:ss.SSS]". That looks promising if I can determine how to change the format, but it seems like that might be the wrong way to fix this.

    Any ideas?

    I'm using PDI 5.0.1, Postgres 9.3, SQL Server 2008 R2.


    Thanks,
    Bruce

  2. #2
    lyarbrough Guest

    Default

    This is a long shot but, in your db connection, click on the advanced tab. the second option is a date/time option. Uncheck it. Save.

  3. #3
    Join Date
    Jul 2009
    Posts
    476

    Default

    It looks like you are passing all of your columns as String. The values for id, folder_id, and several other integer columns are expressed as strings in your INSERT statement. Are you deliberately casting everything as a string? I would start out with just a simple SELECT from SQL Server in my Table Input step, with no datatype conversions, and a simple assignment of the values in Table Output step for Postgres (or whichever output step you are using). There might be some datatype issues since they are different databases, but that's where I would start.

  4. #4
    Join Date
    Jul 2014
    Posts
    2

    Default

    Thanks for the suggestions. I tried both of them, but to no avail. I did find a solution, though I'm not exactly sure what the problem was. I was using the open source jdbc drivers for SQL Server, I switched to the MS ones and everything started working. I'm assuming the open source driver doesn't support the date datatype, though that still doesn't explain why it wouldn't work after I changed it to a datetime. In any event it is working for me now.

    Thanks,
    Bruce

  5. #5
    Join Date
    Feb 2011
    Posts
    152

    Default

    I had this issue and am using the JTDS driver to talk to MSSQL. For us the source data type was really a datetime2, not a regular datetime. We had to alter the metadata on the stream to get that working.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.