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.

 

[ginkoba]

I did change them to datetime and tested but still got the same results as with your formula. TRIRIGA shows 739 for period 12/1/09-12/31/09 and crystal shows 759.
Now do I just keep the datetime parameter as date+00.00.00 in the parameter when I select or change it to say 11:59.59 for start and "date+00:00:00" for enddate?

 

[CoSpringsGuy]

Im pretty much at a loss...

Why dont you create this formula
if {@planned_enddt} >= datetime(2009,12,1,0,0,0)
then 0 else 1

add that formula and {@planned_enddt} to your details section then run the report using 2009-11-30 for your start date. Sort by the formula above .... look through the records that return a 1 and see if you can find the records that have a 1 and a date that should have been picked up. Maybe you can do some troubleshooting that way. You may have to run it a few times tweaking the time portion of the parameter.

The only suspicion i have has to do with seconds plus or minus 12AM. Other than that the formula LBass mentioned should return the same as your software. Any chance you results are actually better than the software?

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

 

[ginkoba]

I don't think they are better than the software because when I run the sql in oracle, I get the exact same result as the tririga app but when I go to Crystal, I get a different couont. In oracle I don't convert the java date as it understands the java date.

See SQl below that I ran in Oracle:

SELECT T1.triIdTX AS T1_1181, T1.triStatusCL AS T1_1183, T1.triStatusCLObjId AS T1_1183_OBJID,

T1.triTaskTypeCL AS T1_1431, T1.triTaskTypeCLObjId AS T1_1431_OBJID, T1.triMatrixRequestClas AS T1_1536,

T1.triMatrixRequestClasObjId AS T1_1536_OBJID, T1.triPlannedEndDT AS T1_1442, T1.triNameTX AS T1_1178,

T2.triNameTX AS T2_1527_SID_1_60,

T1.smiManagingOrgLI AS T1_1649, T1.SYS_TYPE1 AS T1_SYS_TYPE1, T1.SPEC_ID AS T1_SPEC_ID

FROM T_TRIWORKTASK T1

LEFT OUTER JOIN T_ORGANIZATION T2 ON T1.HasResponsibleSysKey2 = T2.SPEC_ID

WHERE T1.SYS_PROJECTID = 1

AND T1.triTaskTypeCL = 'Planned'

AND T1.triPlannedEndDT >= 1259643600000

AND T1.triPlannedEndDT <= 1262235600000

AND T1.SYS_OBJECTID > 0

ProjectID and ObjectID do not make any difference in the count as I have tried that already in crystal.

 

[lbass]

What datetimes do you think that should translate to? Using datetime(1970,1,1,0,0,0), I get a range of 12/1/09 5:00:00am to 12/31/09 5:00:00am--a 30 day period, but not the entire month, since it omits times before 5am on 12/1 and after 5am on 12/31.

-LB

 

[ginkoba]

Good catch LB. I will look into this tomorrow in the AM and let you know.

 

[CoSpringsGuy]

That makes sense LB.. I have been playing with this one on and off all day and assumed it there was a problem with times but I never thought to look at from 0,0,0.

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

 

[ginkoba]

LB and CoSpringsGuy,
Found out yesterday after some thorough research that the TRIRIGA app stores data in EST no matter what timezone the user is in.
This record in question (1077478) has "1259632800000" as it's planned end date value; this is 11/30/2009 9:00 PM Eastern time, but is 12/1/2009 2:00 AM in GMT. TRIRIGA will use the timezone of the logged in user when formatting/displaying records, but it looks like Crystal is using GMT.
If this is so, how can we account for EST in our calculations?

 

[lbass]

There is a function:

shiftdatetime(inputDateTime,inputTimeZone,outputTimeZone)

Maybe you could apply that to the datetime resulting from using the dateadd function.

-LB

 

[ginkoba]

LB,

Can you do me a quick example of this please?

 

[lbass]

Please look this up in the Help section, which is quite good.

-LB

 

[ginkoba]

Got it now LB,
This is what I used:
ShiftDateTime ({@planned_enddt}, "UTC,000,UTC", "EST,300,EDT")

{@planned_enddt} =
dateadd("s",{Command.TRIPLANNEDENDDT}/1000, datetime(1970,1,1,0,0,0))

thanks much

 

[ginkoba]

LB and CoSpringsGuy,

I have finally got this working it seems after some testing. Please see my final formula below:

I'm converting from java datetime(milliseconds) to regular UTC/GMT datetime and then again converting from UTC to EST/EDT all in one go.

ShiftDateTime(DateAdd("s",{table.field/java number}/1000,Date(1970,1,1)),"UTC,0", "")

The "UTC,0", "" at the end will take care of the 4/5 hrs shift in standard or daylight time.

Thanks much you guys.