0

There is a column where values are in dd/mm/yyyy format and at some places there exists a date which are in format of yyyy-mm-dd, when I try to store this in a database table it gives me an error, while creating this table I had mentioned the datatype of that column as "Date", how do go for this?, any help would be much appreciatedthis is date Error

Create table

Deepesh
  • 820
  • 1
  • 14
  • 32
  • 1
    as a guess: is it possible that the problem is the text file input step and not the database-output? maybe you just have to format the input in excel or something before import in pentaho to get a unified date-format? – Seb Apr 27 '16 at 07:37
  • this question isnt about programming .... its about Pentaho software usage. – specializt Apr 27 '16 at 13:51
  • @Deepesh Any news on that? – Seb Apr 29 '16 at 09:31
  • yes I tried inserting the column as string and then used select values to change the format – Deepesh Apr 29 '16 at 09:35
  • ... but that didn't work, I guess. – Seb Apr 29 '16 at 10:23

4 Answers4

0

treat this column as string and fix the format with some javascript step format, outputs as date from js step before the insert in the database.

you have a lot of predefined js functions to format strings and dates.

Changing date format in Pentaho using javascripting

Community
  • 1
  • 1
jacktrade
  • 3,125
  • 2
  • 36
  • 50
0

It seems that the error is caused by different date-formats from your input-file and should not be connected to the following database-steps.

The problem of different date formats for the input-csv can be solved by editing the source file. Just unify the date format to 'dd/MM/yyyy'; I recommend Excel or LibreOffice for this: unify the format here, than copy everything in an text editor and save it as CSV. Now get the fields one more time, the input-step should not generate an error if every date is in the same format.

Another option is - as mentioned by "jipipayo" - to import the date as a string. You will not get any error and can try to get in an unique format with Javascript-step. Or you try to get it as it is in your database. MySQL is probably not as strict as pentaho. Check it out.

Seb
  • 329
  • 1
  • 8
0

Brother, before saving to the database, use the select step and in the Meta-data tab change the datatype of the date column or string date column to DATE and select the FORMAT of the date whatever you wanna save to the database.

Cheers :)

shzyincu
  • 409
  • 4
  • 21
0

In Pentaho i would do it like this:

Input excel - Fields - set for every date field the type to date and change to format to how it is in your excel (you could have 4 date fields in your excel all with different formats)

Next step:
select values - in meta data set the 4 fields to the format you like (for example dd-mm-yyyy)

This way all your date fields are defined the same way

user20645571
  • 1