We help IT Professionals succeed at work.

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

angel7170
angel7170 asked
on
Medium Priority
2,124 Views
Last Modified: 2012-05-06
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
Comment
Watch Question

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))


Author

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

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)))

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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.
 


Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

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

GMT does change with daylight savings.

mlmcc

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.