Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: converting to timestamp

  1. #1
    Join Date
    Sep 2017
    Posts
    27

    Default converting to timestamp

    I have an input file with a date in a format that looks like this

    2018-04-18-15.30.16


    I need to pass that data in Transformation and convert it to Timestamp before inserting that data into Table Output.
    As you can see, that format doesn't t match the Timestamp datatype. What is the best way to convert? Use Select Value step or
    in Table Output step (there is a Sql button there.. is that when it comes to play?) Please advise.

    Thank you,

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

    Default

    Your choices are actually to convert it in the File Input step or use a select values step. Either one would work, and both support error handling.
    Table Output won't allow you to change the format to a timestamp.

    The next question... Do you actually mean Timestamp (Date, Time, milleseconds), or do you mean a Date & Time?
    Once you have the data in the appropriate form (Date - which includes time; or Timestamp), the database will understand what to do with it, no further conversion is necessary.

  3. #3
    Join Date
    Sep 2017
    Posts
    27

    Default

    I actually mean Timestamp (DATE, Time, Fractional seconds). The data definition for the particular field in Oracle Table is Timestamp NULL...I need to write data to that field

    so... it needs to be converted before it gets to the table.

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

    Default

    You haven't said what version of PDI you are using.

    When you bring data into PDI (Text File Input...) you specify what type of data it is. In recent versions of PDI, you can specify Timestamp as a type. You then tell PDI how to interpret the string of data. (Type: Timestamp Format: yyyy-MM-dd-HH.mm.ss) Even though it still *LOOKS* wrong, it is now a Timestamp and your DB will handle it correctly.


    Alternatively, in your Text File Input, you can bring the data in as a String, and then convert it with a Select Values later.

  5. #5
    Join Date
    Sep 2017
    Posts
    27

    Default

    I'm using PDI 8.0

    Thanks,

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.