Change Value in .csv File to Date Datatype

Is there a way I can take a value that represents a date from a .csv file and change the data type to a Date? When I use a .csv file as the datasource of a dataset, the value is being pulled into the dataset as a String and I am having trouble figuring out how to change the format of the value. The date in the .csv file is in the format as day-month-year (for example: 5-Feb-12). I would like to be able to change the format to display the date as month-day-year in this format: 2/5/2012 in a BIRT report. The reason I want to change the format is so that I can create a report to analyze the data and import the output into another database. 
 
I tried changing the Type from String to Date in the Output Columns section of the Edit Data Set window, and I received this error message: 
A BIRT exception occurred. See next exception for more information. Can not convert the value of 5-Feb-12 to java.sql.Date type. 
 
I also tried changing the Type from String to Date in the Select Columns section of the Edit Data Set window, and when I clicked on Preview Results, all values for the columns where I changed the Type were blank where there should be values.

Find more posts tagged with

Comments

mwilliams

Have you tried creating a new SimpleDateFormat with the java.text package? You'd create a new date format of "d-MMM-yy", parse your string with the new format in a computed column with the date type. That should return you a date type as a field in your dataSet. Then, you can use BIRT's standard date formatting to change the format to whatever you want. Let me know if this doesn't work. The following devShare should help, but if you need a simple example, let me know your version.

http://www.birt-exchange.org/org/devshare/designing-birt-reports/203-convert-string-date-to-date-object-in-birt/

kpelzer29

Hi mwilliams, 
 
Thank you for the suggestion! I had not tried creating a new SimpleDateFormat with the java.text package. I used the same code in the Devshare article you provided the link to, but I just had to change the line of code: 
 
df = new Packages.java.text.SimpleDateFormat("yyyy-MM-dd"); 
to 
df = new Packages.java.text.SimpleDateFormat("dd-MMM-yy"); 
 
 
since the String was in the format day-month-year. Then I used BIRT's standard date formatting to change the format. It works- thank you! 
 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="96587" data-time="1330040786" data-date="23 February 2012 - 04:46 PM">
Have you tried creating a new SimpleDateFormat with the java.text package? You'd create a new date format of "d-MMM-yy", parse your string with the new format in a computed column with the date type. That should return you a date type as a field in your dataSet. Then, you can use BIRT's standard date formatting to change the format to whatever you want. Let me know if this doesn't work. The following devShare should help, but if you need a simple example, let me know your version. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/203-convert-string-date-to-date-object-in-birt/'>http://www.birt-exchange.org/org/devshare/designing-birt-reports/203-convert-string-date-to-date-object-in-birt/</a> </blockquote>