asked on

How to convert a Julian date to normal date in format mm/dd/yyyy hh:mm:ss

Hi,

How to convert a Julian date to normal date in format mm/dd/yyyy hh:mm:ss using Crystal reports formula?

The data stored for date fields are in format like:
1,222,196,236.00
1,222,196,178.00
Any help is greatly appreciated? Thank you

dougvarga

Are you sure these are julian dates? It looks like a UNIX date stamp to me.....

try this:

//Add Seconds to Jan 1, 1970
DateAdd("s",{mydatabase.myfield},Date(1970,01,01))

angel7170

ASKER

Thank you so much for your reply.

You are right. It is a Unix date stamp.

But when I used your formula it converted
1,220,560,264.00 - 9/4/2008 8:31:04PM but the date that shows on the application is 9/4/2008 4:31:04 PM.

What could the difference in the time?

please help?

Thank you

ASKER CERTIFIED SOLUTION

dougvarga

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

angel7170

ASKER

Thanks again.

I tried it, but there is like an hour difference.
So I tried using, DateAdd("h",-4,DateAdd("s",{mydatabase.myfield},Date(1970,01,01)))
It pulled the correct date. Is this correct? Will it affect any daylight saving time later?

Please advice.

Mike McCracken

If you go to daylight savings time, you will have to account for the time change.

GMT does change with daylight savings.

mlmcc

nitadmin

or else you can directly use
ShiftDateTime(DateAdd("s",{mydatabase.myfield},Date(1970,01,01)),"UTC,0", ""),
it will take care 4 and 5 hours daylight time saving issue