Hitachi Vantara Pentaho Community Forums
Results 1 to 11 of 11

Thread: Error parsing date 0000/00/00-00:00:00

  1. #1

    Default Error parsing date 0000/00/00-00:00:00

    Hello my problem is that if I disable " Be Lenient when parsing Dates ?" Checkbox
    the parser sends an error with this kind of date, but I would prefer a date with all zeros rather than a lenient date "invented".... 0002/11/30-00:00:00 is the result with this checkbox marked.
    Is 0000/00/00-00:00:00 an unvalid date? will it cause problems later when I will load it on an MySQL database, Previews rows parser sends this message

    Couldn't parse field [Date] with value [0000/00/00-00:00:00], format [yyyy/MM/dd-HH:mm:ss] on data row [1]

    from XX by buildguy) : pwdLastSet String(19) : couldn't convert string [0000/00/00-00:00:00] to a date using format [yyyy/MM/dd-HH:mm:ss]
    2011/02/14 14:01:18 - Adirectory input.0 - ERROR (version 4.1.0-stable, build 14410 from XX by buildguy) : Unparseable date: "0000/00/00-00:00:00"

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

    Default

    That one can only come from MySQL :-)
    There is an option in the JDBC driver to consider these dates to be null.

    --> zeroDateTimeBehavior = convertToNull

    You can set it in the Database Dialog for the MySQL connection.

  3. #3

    Default

    where do I change this, i think is the kettle parser it fails while parsing before loading it into database,
    in com.mysql.jdbc.Driver will I have to change it

  4. #4
    Join Date
    Nov 2008
    Posts
    143

    Default

    Go to where you created the database connection in kettle, then edit it.
    Select Options and add it at the Parameters grid.

  5. #5
    Join Date
    Jul 2012
    Posts
    6

    Default

    Hi, I've got the same problem. Carried out the suggestion of zeroDateTimeBehavior = convertToNull - this had no effect.

    Any other suggestions? Many thanks

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

    Default

    You have to specify it in the Options of the database dialog.

  7. #7
    Join Date
    Jul 2012
    Posts
    200

    Default

    Hi

    I have faced this similar issue when i am working in project i resolved it with whatever Matt has suggested you.. I have resolved in jndi configuration (which is in jdbc.properties file) i.e appending the url with the
    ?zeroDateTimeBehavior = convertToNull

    this will work i have resolved by doing this..

    Quote Originally Posted by masaveu View Post
    Hello my problem is that if I disable " Be Lenient when parsing Dates ?" Checkbox
    the parser sends an error with this kind of date, but I would prefer a date with all zeros rather than a lenient date "invented".... 0002/11/30-00:00:00 is the result with this checkbox marked.
    Is 0000/00/00-00:00:00 an unvalid date? will it cause problems later when I will load it on an MySQL database, Previews rows parser sends this message

    Couldn't parse field [Date] with value [0000/00/00-00:00:00], format [yyyy/MM/dd-HH:mm:ss] on data row [1]

    from XX by buildguy) : pwdLastSet String(19) : couldn't convert string [0000/00/00-00:00:00] to a date using format [yyyy/MM/dd-HH:mm:ss]
    2011/02/14 14:01:18 - Adirectory input.0 - ERROR (version 4.1.0-stable, build 14410 from XX by buildguy) : Unparseable date: "0000/00/00-00:00:00"
    Thanks & Regards
    yvkumar

  8. #8
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    All you can learn from this thread is, there is a JDBC driver option "zeroDateTimeBehavior" that reads a MySQL zero date as NULL.
    It's a feature to allow sane exports of legacy MySQL data for further processing with tools that don't support zero dates.

    Unfortunately, that's not really helpful, because the original poster was asking for a way to "import" zero dates into MySQL.
    Comes voodoo master RickM and revives this dead thread, stating he has the same problem.

    If you really want to cling to this MySQL idiosyncracy, change the input data type from DATE to STRING.
    That way it's possible to import zero dates into MySQL, but think twice.
    So long, and thanks for all the fish.

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

    Default

    You would have to add the option to the URL in the JNDI spec if you're inclined to use that. (not something I personally can recommend)

  10. #10
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by MattCasters View Post
    You would have to add the option to the URL in the JNDI spec if you're inclined to use that.
    The way you phrase it, one could get the feeling that JNDI must be selected as access method, but the option works well with JDBC access, too.
    So long, and thanks for all the fish.

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

    Default

    The options specified are usually appended to the JDBC URL or to the connection properties (in Oracle's case for example). That works fine if you use native JDBC.
    However, with JNDI you have to set the URL and class yourself so we expect you to set the options as well.

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.