Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: String to Date Conversion issue

  1. #1
    Join Date
    Jun 2016
    Posts
    102

    Talking String to Date Conversion issue

    Hi Experts,

    We have string data with UTC date format so how can i convert these data into date format.

    sample input:

    04/28/2011 08:48:51 UTC

    required output:

    2011/04/28 08:48:51.000


    I tried with select values step but i got the following error.
    can anyone please suggest me how to convert.

    Name:  1.jpg
Views: 1246
Size:  9.9 KB

    2017/02/09 16:03:11 - Select values.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : org.pentaho.di.core.exception.KettleConversionException:
    2017/02/09 16:03:11 - Select values.0 -
    2017/02/09 16:03:11 - Select values.0 - created_at_with_timezone String : couldn't convert string [04/28/2011 08:48:51 UTC] to a date using format [yyyy/MM/dd HH:mm:ss.SSS] on offset location 19
    2017/02/09 16:03:11 - Select values.0 - 04/28/2011 08:48:51 UTC

    Name:  2.jpg
Views: 1248
Size:  10.4 KB


    Thanks
    janu
    Last edited by Jahnavi; 02-09-2017 at 06:39 AM.

  2. #2
    Join Date
    Jan 2015
    Posts
    107

    Default

    The Format string tells the step what to expect in the string field, so it should reflect the data you *have* in the string, not the data you *want*.

    In this case you should select or manually enter "MM/dd/yyyy HH:mm:ss zzz". See also: http://wiki.pentaho.com/display/Repo...rmatting+Table

    Once it's converted to Date format, Pentaho will be able to deliver the correct format to most (database) outputs. Only if exporting to text files again will you need to specify the output format and then you use the one you posted in the question.
    Last edited by Isha Lamboo; 02-09-2017 at 11:08 AM. Reason: added comment on output formatting

  3. #3
    Join Date
    Jun 2016
    Posts
    102

    Default

    Thanks Isha. Its 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.