CODEWITHSUNDEEP
datepentahokettledate-conversionpentaho-spoon
Deepesh
Deepesh

Reputation: 840

Converting date yyyy-mm-dd to dd/mm/yyyy format in pentaho and store in a database

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

Upvotes: 0

Views: 7637

Answers (4)

user20645571
user20645571

Reputation: 1

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

Upvotes: 0

shzyincu
shzyincu

Reputation: 419

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 :)

Upvotes: 0

Seb
Seb

Reputation: 339

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.

Upvotes: 0

Otto
Otto

Reputation: 3294

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

Upvotes: 0

Related Questions