Formula needed

[cardimom]

Hi Crystal reports shows a date/time value as 1.27e+017. The data is shown as 127264599743590905 in the database table. How do I convert this to a readable date/time?

Thanks
Di

 

[synapsevampire]

This is the sort of thing you'd learn from your dba or vendor.

If you had an idea of what the result should be, we might figure it out.

Generally it's the number of seconds from some point in time, but without knowing that date one can only guess.

The length of this value makes me nervous too, perhaps it's milliseconds, but check with the developer/vendor.

The formula might be something like:

dateadd("s",(127264599743590905/1000),cdate(1970,1,1))

-k

 

[boeloe]

You haven't told us what system and database are involved.

If it is "unix time" then you will have to get hold of the u2lpdmtime.dll from the business objects support site.
Then simply create a new formula and drag it onto your report:

pdmtimetodatetime({table.fieldname})

 

[cardimom]

Thanks for your replys. The database is Veritas Netbackup Professional. A Veritas report from within the Veritas console comes up correctly and shows the time a backup started as m/d/yyyy h:m:s am or pm so 3/30/2004 8:16:24PM.When I export the report to crystal the report will not open and says there are errors. When I try to get a time/date like backup time started or ended when making my own report in crystal I get 1.27e+017 for all time/date fields. When using a sql script in Interactive Sql I get this format 127264599743590905 and they are all different so they must be correct. Veritas uses a formula If isnull({Machines.LastCompletedBackupTime}) then
"Never"
else
ToText(NBPUINT64ToLocalizedDate ({Machines.LastCompletedBackupTime}))
but it does not work in Crystal. Veritas says its a crystal problem and crystal says its a veritas problem. The default veritas reports can not be modified in veritas's console that is why I want them in crystal I used to be able to do this, before updating to the newest veritas software version.