Convert decimal number to date

Hello, I need to know how to convert a decimal number field to a date. The number field comes from the database and when I try to change the data type of the field in BIRT, I get an error saying that the data cannot be converted to date time. Any help would be appreciated.

Find more posts tagged with

Comments

micajblock

How is the decimal number stored. Please provide details.

josh.flaumenhaft

What do you mean how the decimal number is stored? What are you information are you looking for?

micajblock

Decimals are not dates. How would you expect BIRT to convert this? Is the Decimal number in seconds from a base date? What is the base date?

josh.flaumenhaft

alright, let me provide some backgroud. I am using BIRT to build reports for my TRIRIGA application with an MSSQL server on the back end. The field in question is configured as a date field in TRIRIGA but the data is stored as as Decimal numbers. I need to know, either in SQL or BIRT if there is a way to convert the decimal format of the date into a date time format.

micajblock

can you provide some sample dates and their decimal values?

josh.flaumenhaft

for example, the date 04/24/2014 shows in the database as 1398319200000 and 5/29/2014 shows in the database as 1401343200000

micajblock

what about date time? Do you have any of these?

josh.flaumenhaft

no, there is no time, just date

micajblock

It turns out to e simple. Create a computed field like this:<pre class="_prettyXprint">new Date(row["datefield"])</pre>

josh.flaumenhaft

that woked, thank you very much!!

tomcat711

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="127068" data-time="1396370669">
<div>
 
It turns out to e simple. Create a computed field like this:
<pre class="_prettyXprint">
new Date(row["datefield"])</pre>
</div>
</blockquote>
How would you convert decimal to date if decimal date is stored like yyyymmdd (20150321)? Thanks

JFreeman

There are probably quite a few solutions to this.
Here's a way using both native javascript date object as well as a BIRT Date/Time object when creating a computed column.
 
Native javascript:
<pre class="_prettyXprint _lang-js">
var myDate = row["datefield"];
new Date(myDate.substring(0, 4), (parseInt(myDate.substring(4, 6)) -1), myDate.substring(6));
</pre>
BIRT Date/Time:
<pre class="_prettyXprint _lang-js">
var myDate = row["datefield"];
BirtDateTime.date(myDate.substring(0, 4), (parseInt(myDate.substring(4, 6)) -1), myDate.substring(6));
</pre>

Siva Rao

how can i convert a string(parameter value) to time format in BIRT report