Handling date format in Pentaho using javascripting

Question

My input excel sheet has field "date" with two different types of values as shown below

2015-03-02 11:06:35

3/2/2015  4:03:53 AM

I am reading them as "string" and performing below logic

var temp =  date.getString();
temp = str2date(temp,"dd.MM.yyyy HH:mm:ss");

I get the below error

*Could not apply the given format dd.MM.yyyy HH:mm:ss on the string for  2015-03-02 11:06:35  : Format.parseObject(String) failed*

I tried reading them as "date" , but I got the below error

Unparseable date: " 2015-03-02 11:06:35 "

How can we handle this error?

Answer 1

A couple ideas from the docs. http://wiki.pentaho.com/display/EAI/JavaScript+Values

First try calling getDate() it should be a function associated with the cell in question.

If that doesn't work try calling getNumber() in excel all dates are represented in floating point. The whole number part is days since jan 1 1970 and the fraction part is the percentage of the way through the day. I'm sure if you look around there is a js wrapper around this functionality.

Another idea would be to determine all the numberformat strings. Loop over them catching errors. The one that doesn't throw an error should be the correct one. Or you could write a small regex pattern to better examine the dates coming out.

Answer 2

Finally, I got result. I will explain step by step please follow steps very carefully.

Step 1: Data Grid => I created one field called fail_date i.e String datatype.

Step 2: Modified Java Script Value => I wrote some code like these.Please follow screen shot.

Step 3: Select values => Here i converted date datatype.

Step 4: Modified Java Script Value 2 => Here also i wrote one code.Please follow screen shot.

Step 5: Select values => Finally, I converted require data format.here correct_date field is your required output.

Step 6: Final output screen will be like these.

Step 7: Below screen shot show whole transformation screen.

Hope this helps.

Answer 3

Can you use a library. I've used xdate its really good. And it parses both of these date strings

var xdate = require('xdate');
xdate('2015-03-02 11:06:35').toDate(); // returns a js date object
xdate('3/2/2015  4:03:53 AM').toDate(); // returns another js date object.