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

Posted on 2009-02-12
Last Modified: 2012-05-06

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:
Any help is greatly appreciated? Thank you
Question by:angel7170
    LVL 10

    Expert Comment

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

    try this:

    //Add Seconds to Jan 1, 1970


    Author Comment

    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

    LVL 10

    Accepted Solution

    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

    Author Comment

    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.

    LVL 100

    Expert Comment

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

    GMT does change with daylight savings.

    LVL 8

    Expert Comment

    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

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Problem to adjust sheet 1 79
    Using YubiKey with REST API application 2 55
    countX 22 53
    Crystal Reports - Trim Data 7 25
    Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
    This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now