Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Strange date problem with insert update step

  1. #1
    Join Date
    May 2011
    Posts
    23

    Default Strange date problem with insert update step

    I have an insert update step which should update a table on a postgres 8.4 server. The update record has four dates, that need to be compared. The PK is a bigint. The problem seems to arise, when comparing the dates to find wheter an update is required or not. The problem seems to be a dateformatting issue:

    2011/10/26 09:54:07 - Insert / Update.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Date<binary-string> : Unable to compare with value [Date]
    2011/10/26 09:54:07 - Insert / Update.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) :
    2011/10/26 09:54:07 - Insert / Update.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Geöffnet String : couldn't convert string [2011/10/19 16:59:00.000] to a date using format [dd.MM.yyyy HH:mm]
    2011/10/26 09:54:07 - Insert / Update.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Unparseable date: "2011/10/19 16:59:00.000"
    2011/10/26 09:54:07 - Insert / Update.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) :

    The crazy thing is, that the input data is from a CSV file which has a german date formatting like the one mentioned in the error (dd.MM.yyyy HH:mm). However just after reading the CSV file I have a Select Value step, which removes unnecessary fields and makes a conversion to a dateformat like yyyy/MM/dd HH:mm. If I run a preview it works just fine, the dates are correctly formatted.

    I found a thread here about two years back, who discussed the same problem. But there was no solution or it was not treated as an important problem.

    So could anybody tell me where the problem could arise? If I select the data from my PostgreSQL db, dates are correctly shown. Where does this dateformat dd.MM.yyyy HH:mm come from??

    Any help would be greatly apreciated!

    Thomas

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    couldn't convert string [2011/10/19 16:59:00.000] to a date using format [dd.MM.yyyy HH:mm]
    This sounds like a correct statement since the date format is wrong for the supplied string.
    That being said, lazy conversion is active so I would convert to normal storage for that column (or not use it at all).

  3. #3
    Join Date
    May 2011
    Posts
    23

    Default

    Hi Matt,

    Sorry but I do not understand your answer. I cannot leave out these date fields because I need to update them!

    However it seems the problem has gone away. Looks like this is a caching problem. I have the kettle repository also on a postgres server. I did a select value step that changes the format to yyyy-MM-dd HH:mm. This change was active only after I closed SPOON and restarted it. Now it works as expected. Looks like the changes in the transformation are cached somewhere and not updated if you safe it. Any idea about this? Should one turn off the cache when developing transformations?

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Click right on the database connection, pick the "Clear cache" option to be sure nothing's cached anymore. Restarting Spoon doesn't help generally.

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.