Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: couldn't convert string [2018-1-0] to a date using format [yyyy/MM/dd HH:mm:ss.SSS]

  1. #1
    Join Date
    Jun 2016
    Posts
    179

    Default couldn't convert string [2018-1-0] to a date using format [yyyy/MM/dd HH:mm:ss.SSS]

    Step "Database lookup" or "Insert/Update", I'm trying to check or insert date which is string "2018-1-0' (0 as day is correct format for MySQL) and I receive error:

    Code:
    Database lookup.0 - DateShort String : couldn't convert string [2018-1-0] to a date using format [yyyy/MM/dd HH:mm:ss.SSS] on offset location 4
    Why it is performing this check? Date is just text to insert. Im not using format yyyy/MM/dd HH:mm:ss.SSS anywhere in transformation....

  2. #2
    Join Date
    Apr 2008
    Posts
    4,689

    Default

    Always match your types if you don't want to risk getting a default format applied when converting between types.
    The default format for String to Date is yyyy/MM/dd HH:mm:ss.SSS

    In other words, make it a Date before you try to do the Database lookup or Insert/Update.
    However, 2018-1-0 will either fail, or become 2017-12-31

  3. #3
    Join Date
    Jun 2016
    Posts
    179

    Default

    Thank you for help.
    I have to find another solution; I do not need day information... probably I will always set day to first day of the month.
    Last edited by Gosforth; 01-16-2018 at 09:40 AM.

  4. #4
    Join Date
    Apr 2008
    Posts
    4,689

    Default

    You can use the Select Values step to make the string into a Date
    Is that not the solution you are looking for?

  5. #5
    Join Date
    Jun 2016
    Posts
    179

    Default

    Thanks, this is what what I do for other transformations.

    The desired format for MySQL in this project is "2018-1-0"
    I understand that if I produce such date Pentaho will cast error as not correct...

    Quote Originally Posted by gutlez View Post
    You can use the Select Values step to make the string into a Date
    Is that not the solution you are looking for?

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

    Default

    Generally, don't use MySQL date columns to store values only MySQL does understand.
    You run into problems when implicit mappings from MySQL date to JDBC date and vice versa enter the game.
    You can create a MySQL view with the date converted to a string if you only need to read such values.
    Otherwise you're out of luck, I'm afraid.
    So long, and thanks for all the fish.

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.