Converting YYYYMMDD to MM/DD/YY

When I have a single data set, I am able to move my integer date value to a string field, then sub-string the string field to show as 01/01/01 in the computed columns. I am now using a Joint Data Set, and I am unable to do this. Is there a simple way to convert 20120224 to 02/24/12 in my detail rows?

Find more posts tagged with

Comments

mwilliams

20120224 is an integer? Is this correct? 
 
If so, you can probably do something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
importPackage (Packages.java.text);

df = new SimpleDateFormat("yyyyMMdd");
df.parse(row["myIntegerDate"].toString());
</pre>
 
If you do this in a computed column in your dataSet or just as a new binding in your chart, this should return a date, which you can now use the formatDateTime feature in the property editor to format the date however you'd like, in your design. 
 
Hope this helps. Let me know if you have questions.

briann

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="96646" data-time="1330107860" data-date="24 February 2012 - 11:24 AM">
20120224 is an integer? Is this correct? 
 
If so, you can probably do something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
importPackage (Packages.java.text);

df = new SimpleDateFormat("yyyyMMdd");
df.parse(row["myIntegerDate"].toString());
</pre>
 
If you do this in a computed column in your dataSet or just as a new binding in your chart, this should return a date, which you can now use the formatDateTime feature in the property editor to format the date however you'd like, in your design. 
 
Hope this helps. Let me know if you have questions. </blockquote>

briann

Great, thank you. 1 question. Where do I put this code? Do I add it as text in the detail row?

importPackage (Packages.java.text);
df = new SimpleDateFormat("yyyyMMdd");
df.parse(row["myIntegerDate"].toString());

mwilliams

Either as the expression for a computed column in your dataSet, obviously changing "myIntegerDate" to whatever you dataSet field actually is. Or you could use that as an expression in a new data binding you add to your table. Then, use a data element to display the new field and use the formatDate section of the property editor to format the new date how you'd like it. Let me know if you have more questions!

briann

I can't do anything to the 2nd dataset for some reason. I can't substring any of the fields. I can however, substrings fields in the 1st dataset. Does this have anything to do with it? Typically, I just create a computed column, when using 1 dataset, that substrings the iso date, and converts it to usa date. So my date would be 20120101, and I would substring that field, once it is moved to a string field, and create 01/01/12.

mwilliams

Can you please attach your report design?

briann

Here you go. In dataset MSTWIPW there is a field WIDELP. It is an 8,0 date, example: 20120101. Like I said, I typically move this field to a string field, and substring it to look like 01/01/12 using a computed column, and this has worked fine for me in other reports. But this report is using a Joined Dataset, and that seems to be the problem.

briann

Trying to load the converted date in the 9th column. My report is attached to an IBM server which contains my datasets.

mwilliams

Ok, I added a computed column to your joint dataSet that should create the date field for you. Since I don't have access to your data, this is the furthest I can go, but you should just need to drag the new field "actualDate" over from your joint dataSet into your column. Then, select the data field and go to the formatDateTime section of the property editor and format the date how you want it and run your report. That should give you what you're wanting. Let me know if you're having issues with this still, after doing the above.

briann

I doing preview results on my dataset and here is what I'm getting:

df = new SimpleDateFormat("yyyyMMdd");

df.parse(row["datefmtf"].toString());":
Wrapped java.text.ParseException: Unparseable date: "0" (unnamed script#3)

at org.eclipse.birt.data.engine.impl.ComputedColumnHelper$ComputedColumnHelperInstance.process(ComputedColumnHelper.java:523)

at org.eclipse.birt.data.engine.impl.ComputedColumnHelper.process(ComputedColumnHelper.java:126)

at org.eclipse.birt.data.engine.executor.cache.RowResultSet.processFetchEvent(RowResultSet.java:160)

at org.eclipse.birt.data.engine.executor.cache.RowResultSet.doNext(RowResultSet.java:121)

at org.eclipse.birt.data.engine.executor.cache.RowResultSet.next(RowResultSet.java:91)

at org.eclipse.birt.data.engine.executor.transform.SimpleResultSet.initialize(SimpleResultSet.java:177)

at org.eclipse.birt.data.engine.executor.transform.SimpleResultSet.<init>(SimpleResultSet.java:117)

at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:1009)

at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:441)

at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1124)

at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:232)

at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:173)

at org.eclipse.birt.report.engine.api.impl.ExtractionResults.nextResultIterator(ExtractionResults.java:74)

at org.eclipse.birt.report.designer.data.ui.dataset.DataSetPreviewer.preview(DataSetPreviewer.java:70)

at org.eclipse.birt.report.designer.data.ui.dataset.ResultSetPreviewPage$5.run(ResultSetPreviewPage.java:337)

at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)

Caused by: org.eclipse.birt.data.engine.core.DataException: A BIRT exception occurred. See next exception for more information.
There are errors evaluating script "importPackage (Packages.java.text);

df = new SimpleDateFormat("yyyyMMdd");

df.parse(row["datefmtf"].toString());":
Wrapped java.text.ParseException: Unparseable date: "0" (unnamed script#3)

at org.eclipse.birt.data.engine.core.DataException.wrap(DataException.java:123)

at org.eclipse.birt.data.engine.script.ScriptEvalUtil.evalExpr(ScriptEvalUtil.java:946)

at org.eclipse.birt.data.engine.impl.ComputedColumnHelper$ComputedColumnHelperInstance.process(ComputedColumnHelper.java:486)

... 15 more

Caused by: org.eclipse.birt.core.exception.CoreException: There are errors evaluating script "importPackage (Packages.java.text);

df = new SimpleDateFormat("yyyyMMdd");

df.parse(row["datefmtf"].toString());":
Wrapped java.text.ParseException: Unparseable date: "0" (unnamed script#3)

at org.eclipse.birt.report.engine.javascript.JavascriptEngine.evaluate(JavascriptEngine.java:295)

at org.eclipse.birt.core.script.ScriptContext.evaluate(ScriptContext.java:154)

at org.eclipse.birt.data.engine.script.ScriptEvalUtil.evalExpr(ScriptEvalUtil.java:918)

... 16 more

Caused by: org.mozilla.javascript.WrappedException: Wrapped java.text.ParseException: Unparseable date: "0" (unnamed script#3)

at org.mozilla.javascript.Context.throwAsScriptRuntimeEx(Context.java:1773)

at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:183)

at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:247)

at org.mozilla.javascript.optimizer.OptRuntime.call1(OptRuntime.java:66)

at org.mozilla.javascript.gen.c8719._c0(unnamed script:3)

at org.mozilla.javascript.gen.c8719.call(unnamed script)

at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:398)

at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3065)

at org.mozilla.javascript.gen.c8719.call(unnamed script)

at org.mozilla.javascript.gen.c8719.exec(unnamed script)

at org.eclipse.birt.report.engine.javascript.JavascriptEngine.evaluate(JavascriptEngine.java:290)

... 18 more

Caused by: java.text.ParseException: Unparseable date: "0"

at java.text.DateFormat.parse(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:161)

... 27 more

briann

Disregard my last email, I figured out that issue.

briann

I used your logic on another report I have, that only has 1 dataset, and it worked perfectly. When I try to use it on a Joined Dataset, it doesn't work. For some reason, I cannot substring, and use your logic on the joined dataset computed columns.

mwilliams

I'm going to recreate this with a joint dataSet and an integer date in a way that we can both run it and see if it'll work. Maybe that'll help.

mwilliams

In this report, I create a field in my first dataSet called IntegerDate with an integer value of 20120224. I join this with the next dataSet and then create a computed column to convert the integer date into an actual date field. I then drop this date into the report and use the format datetime section of the property editor to format the date in the form of dd/MM/yy and it works as expected. Take a look and see if this is how everything is in your report. If you can recreate how yours is breaking, in a way I can run it, I'll take a look, but there's no reason a joint dataSet should cause it to not work.