Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: reg: insert/update error convert string [2015/02/26 40:00:00.000] to a date

  1. #1
    Join Date
    Mar 2013
    Posts
    127

    Default reg: insert/update error convert string [2015/02/26 40:00:00.000] to a date

    Hi All,

    I have requirement to update string value 40:00:00 to database table which has timestamp column EmployeeScheduleHoursPerWeek and ended up with error

    HoursPerWeek String : couldn't convert string [2015/02/26 40:00:00.000] to a date using format [yyyy/MM/dd HH:mm:ss.SSS] on offset location 23


    and below update query works fine in mysql workbench/kettle sql editor

    update employee set EmployeeScheduleHoursPerWeek= '40:00:00' where employeeid = '140146'
    (and I cannot cannot change column type in database because of business logic used in applications)

    Name:  Capture.jpg
Views: 124
Size:  15.3 KB

    Is there any way to avoid this error and perform update...
    Any help.. or sugessions will be appreciated...

    t_load_employees_dummy.ktr
    Last edited by MateenSA; 06-26-2015 at 06:05 AM.
    Regards,
    Mateen

  2. #2
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi Mateen,
    if your original value is [2015/02/26 40:00:00.000], you have to split the date part from the time part and then insert in yout db only 40:00:00.000, replicating what you're doing with your query.
    To split that value you can use a Cut String or Calculator, or possibly even a Select Value step.
    -- Mick --

  3. #3
    Join Date
    Mar 2013
    Posts
    127

    Default

    Hi Mick,

    I have hardcoded 40:00:00.000 in datagrid and getting error couldn't convert string [40:00:00.000] to a date using format [yyyy/MM/dd HH:mm:ss.SSS] on offset location 2.

    i have checked this even with update step and execute sql script step and Still facing same error

    Quote Originally Posted by Mick_data View Post
    To split that value you can use a Cut String or Calculator, or possibly even a Select Value step.
    Any other hints will be appreciated....

    Regards,
    Mateen
    Regards,
    Mateen

  4. #4
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi.
    As far as I know, 40 is not an accettable hour. I think that they should go from 00 to 24.
    Note that if you use a format: yyyy/MM/dd HH:mm:ss.SSS, then your data should reflect that format, for example :2015/06/26 23:59:59:001.
    Your data has to be the correct format as it is described in the metadata for that field.
    -- Mick --

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.