Converting number to date

[ginkoba]

I have a date in my database as a number: 1259632800000 (this should be 11/30/09)

I'm trying to convert this to date but each time I do, I get the date off. Here's what's happening. When I apply the following formula:
date({Command.TRIPLANNEDENDDT}/86400000), I get 11/30/1939. What am I doing wrong? Please help.

 

[CoSpringsGuy]

try this
//
date(1969,12,31)+int({Command.TRIPLANNEDENDDT}/86400000)

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[ginkoba]

Thanks much my friend. Very much appreciate this. People like you are some of the reasons why we are always on these tek sites. Thanks again.

Can you just please explain what the formula is doing and I'm assuming this will apply to all date fields like this? correct? Why the 1969 date?

 

[CoSpringsGuy]

Im not sure what system you are on but I think that is a java thing (not sure)...

Java stores its date in milliseconds and is computed from January 1st, 1970

so the current date is equal to the number of milliseconds from 00:00:00 December 31, 1969 (midnight)

Crystal thinks you are starting at January 1st, 1900 which is why there was a 70 year difference.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[ginkoba]

That explanation is exactly what I was assuming. The database is on Oracle and the application is something called TRIRIGA Facilities Management.

Thanks again, really do appreciate it.

 

[ginkoba]

Hi CoSpringsGuy. I am following up on the answers you gave me yesterday. I applied the formula and at first it seemed to work as all the records that had dates of 12/1/2009 were now showing under 11/30/2009 as they should be because it showed that way in the TRIRIGA app. However, when I run a report inside the app for the month of december 2009, I get 6 records less than when I run it in Crystal. Is the date formula still leaving out something ? The crystal report is fresh, no filters other than the date parameters and the date formula to convert the number to date. Also, I am trying to go through the records line by line in a spreadsheet to see which records are different from the ones pulled in crystal. While I'm doing that(and I will report to you what I find), can you please check the formula for me. Thanks much.

(CR XIR2, WinXPSP3, Oracle DB)

 

[CoSpringsGuy]

The only thing different I did on the formula was go from December 31st.... That seemed to bring back the date you wanted in your initial example. Normally when I use that formula it is from January 1st

date(1970,1,1)+int({Command.TRIPLANNEDENDDT}/86400000)

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[ginkoba]

Hi again CoSpringsGuy. Here's what I have been looking into since we communicated last time. Please see below:

date(1970,01,01)+int(1259643600000/86400000)- The date number here is what the app shows for the start date of 12/1/09 when I run a report for the period >=12/1/09 and <=12/31/09. When I use 1970 as above. For the end date of 12/31/09, this is what I have-date(1970,01,01)+int(1262235600000/86400000).

When I use 1969,12,31 as the start date in the formula, I get 11/30/09 and 12/30/09 respectively. Those date numbers from the TRIRIGA app consistently shows the same number for whatever report I run for that date range. There are still some records being left out. For instance, there is a record showing 12/01/2009 13:12:59 as start date and 12/24/2009 13:12:59 as end date but can't find it at all in my crystal reports. Strange but true...wondering if there's another formula that should fix this issue?

 

[CoSpringsGuy]

show your record selection formula...

also show the TRIRIGA date for those that are coming back incorrectly. (the millisecond field)

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[ginkoba]

Record selection formula:

{@planned_enddt} >= {?start}
and
{@planned_enddt} <= {?end}

Date conversion formula ({@planned_enddt}) used in my parameter:
date(1970,1,1)+int({Command.TRIPLANNEDENDDT}/86400000)

Those that are coming back incorrectly, They are all showing up in the TRIRIGA app as 11/30/09 but using the above formula, they show up in crystal as 12/1/09. When I use 12/31/1969, they show up as what TRIRIGA has but then my overall count is higher than what TRIRIGA has. Say by about 6 or so records. :

ID PEDate Status Type Java date number

1053060 12/1/2009 Closed Planned 1259636400000
1073057 12/1/2009 Closed Planned 1259664897000
1077493 12/1/2009 Closed Planned 1259634600000
1077494 12/1/2009 Closed Planned 1259634600000
1077495 12/1/2009 Closed Planned 1259634600000
1077452 12/1/2009 Closed Planned 1259631000000
1077478 12/1/2009 Closed Planned 1259632800000
1077479 12/1/2009 Closed Planned 1259632800000
1077481 12/1/2009 Closed Planned 1259632800000
1077482 12/1/2009 Closed Planned 1259632800000
1077483 12/1/2009 Closed Planned 1259632800000
1077480 12/1/2009 Closed Planned 1259632800000
1077484 12/1/2009 Closed Planned 1259632800000

 

[CoSpringsGuy]

I have to run to a meeting for about an hour. I have two thoughts on this..

first try changing the formula to
date(1970,1,1)+TRUNCATE({Command.TRIPLANNEDENDDT}/86400000)
and see if that works...

if not I have another thought but I havent used Java date and time in a while so i have to go do some reading.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[ginkoba]

Unfortunately, this did not work CoSpringsGuy. I look forward to hearing back from you.
Firstly, it is still showing these 13 records with planned end dates of 12/1/09. This should definitely be showing under 11/30/09 as per the app.

 

[lbass]

I wonder whether you are losing detail by using days in your calculations instead of seconds--since it appears that you are getting datetimes as results. Maybe try:

dateadd("s",{Command.TRIPLANNEDENDDT}/1000, date(1970,1,1))

I'm not sure whether you should be using date(1970,1,1) or possibly a datetime like datetime(1969,12,31,23,59,59).

-LB

 

[ginkoba]

Thanks much LB. I just tried your formulas(both) but still getting those 13 records I spoke about earlier(see thread) showing 12/1/09 as the enddate instead of 11/30/09.

 

[lbass]

Are your parameters set up as dates or datetimes?

-LB

 

[ginkoba]

LB, they are setup as dates.

 

[CoSpringsGuy]

That was my next question was your parameter settings.

if you set up the formula Lbass suggested
//
dateadd("s",{Command.TRIPLANNEDENDDT}/1000,datetime(1969,12,31,23,59,59))

and compare that to a datetime parameter.... I cant see how it doesnt work.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[ginkoba]

LB and CoSpringsGuy, I understand that TRIRIGA stores dates using Java time – number of milliseconds since Jan 1, 1970. Not seconds. Could this be the reason?

 

[CoSpringsGuy]

No
dateadd("s",{Command.TRIPLANNEDENDDT}/1000,datetime(1969,12,31,23,59,59))

does the required conversion to account for milliseconds.

Did you check your parameter types like LBass suggested above?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

oh I did see you answered that question. Change them to datetime parameters instead of date

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection