Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Conversion to date

  1. #1

    Default Conversion to date


    I am migrating an ETL from Cognos Data Manager to PDI (version 3.2). The source data is read from a AS400 DB2 table. This table contains a column with a date in a numeric format.

    • 1100714 (representing 2010-07-14, the first 1 indicates it's a 21st century date)
    • 100714 (representing 1910-07-14)
    To convert this to a date we had in Cognos a function as follows:

    if ApicsDatum = 0
    then return toDate(toChar('99991231'),'YYYYMMDD');
    else return toDate(toChar(ApicsDatum + 19000000),'YYYYMMDD');

    When the date on AS400 is 0 this will return date 99991231, otherwise 19000000 is added to the number so it has the correct century date (e.g. 100714 + 19000000 = 19100714). The toDate function turns it in a valid date format.

    In PDI, in a Calculator, I defined a constant with value 19000000, then a 'A + B' calculation to add both values, then a 'Create a copy of field A' with Value type String. This works fine, I get the date in a string value. I thought that I could convert it to a date too by creating a new 'Create a copy of field A' calculation with Value type Date and Conversion mask yyyyMMdd. But this throws an error (e.g. result String : couldn't convert string [ 19890403] to a date using format [yyyyMMdd]).
    What am I doing wrong?

    A second problem is how to handle the 'if' statement in PDI.


  2. #2
    Join Date
    Oct 2007


    Hello Patrick

    I've attached a sample that convert properly. I was able to reproduce the same error as you so I'm not sure exactly what the problem is but found a way to fix it.

    In my calculator, I had to add an additionnal conversion withe the "RemoveLeadingSpace" as it looks like there was an extra space added at the beginning of the date, afterwards, it convert properly to a date field. You can run the transformation by right clicking on the last dummy step and choosing Preview.

    As for your other question regarding "if", there's various way to do so:
    - Formula
    - Modified Java Script Value
    - User Defined Java Class
    - User Defined Java Expression

    As in example in a javascript, you could use the following:
    MyVariable = A > B ? "Yes" : "No"
    Attached Files Attached Files

  3. #3
    Join Date
    Mar 2010


    Perhaps the leading space in your date string is preventing the conversion. It seems default format for integers is always containing a space prior to the first numeric value... perhaps an intermediate conversion is making that happen.

    Perhaps you can use a format of # for your 19000000 constant?


  4. #4

    Default More difficult function


    Thanks for the help. It solved my problem. But now I have a more complicate function that I need to migrate :

    if ApicsDatum <> 0
    then if ToInteger(ApicsUur) < 2400
    $v_Datum_YYYYMMDD := toChar(ApicsDatum + 19000000);
    $v_Uur__HHMM := lpad(ApicsUur,5) ;
    $v_YYYYMMDD_HHMM := concat( $v_Datum_YYYYMMDD, $v_Uur__HHMM) ;
    return toDate( $v_YYYYMMDD_HHMM, 'YYYYMMDD hhmi');
    if ToInteger(ApicsUur) >= 2400 and ToInteger(ApicsUur) <= 2700
    $v_Datum_YYYYMMDD := toChar(ApicsDatum + 19000000);
    $v_Uur__HHMM := lpad( lpad(toChar(ToNumber(ApicsUur,4,0)-2400),4,'0') ,5);
    $v_YYYYMMDD_HHMM := concat( $v_Datum_YYYYMMDD, $v_Uur__HHMM) ;
    return AddToDate( toDate( $v_YYYYMMDD_HHMM, 'YYYYMMDD hhmi'), 1 );
    else return '0001-01-01 00:00:00';
    else return '0001-01-01 00:00:00'

    It has some nested if statements and works with variables, so I am not sure how to best tackle this. Can anyone put me in the right direction, preferably with an example?

    Many thanks,

  5. #5


    Goeiemiddag Patrick,

    Like CHamel mentioned, the best way to tackle this is using a Javascript step.
    Have a look at the attached example.

    Best regards,

    Attached Files Attached Files

  6. #6


    Goeiemiddag Bart,

    Thanks for your help !


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.