[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2081
  • Last Modified:

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
0
angel7170
Asked:
angel7170
1 Solution
 
dougvargaCommented:
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))


0
 
angel7170Author Commented:
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

0
 
dougvargaCommented:
You have to adjust for time zones.  UNIX dates, I believe, are in GMT....


I am in eastern time which is GMT -5

so i might try:


//Add Seconds to Jan 1, 1970
DateAdd("h",-5,DateAdd("s",{mydatabase.myfield},Date(1970,01,01)))
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
angel7170Author Commented:
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.
 


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

GMT does change with daylight savings.

mlmcc
0
 
nitadminCommented:
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now