Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Calculate mininum between two column dates

  1. #1
    Join Date
    Aug 2010

    Default Calculate mininum between two column dates

    I have a table with 2 fields (date type) and i want to create a new field with the mininum date of the two columns.
    I used the Formula Step with the formula: Min([field1];[field2]) and select date as value type but give me an error. If i choose number as value type it returns data in this format(39725).
    Any suggestions how to solve this problem are welcome.

  2. #2
    Join Date
    Sep 2009


    Hi there,

    you should be able to select the minimum right from the DB. Most databases support date arithmetic so something like the following should work.

    SELECT dat1,dat2,LEAST(dat1,dat2) as min_date FROM mytable;

    If the LEAST function is not available in your RDBMS you can use something like that:

    SELECT dat1,dat2,CASE WHEN dat1<dat2 THEN dat1 ELSE dat2 END AS min_date FROM mytable;

    The case statement should be more compatible. You might have to check whether the < operator works as expected for date types, and adjust respectively.

    If you still want to do it in Kettle, I've attached a sample to show how to do it using a custom java expression


    Attached Files Attached Files

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.