Avatar of Kathy_A
Kathy_A
 asked on

Convert Julian date to 'mm/dd/yyyy'

How do I write the code to convert a julian date to mm/dd/yyyy format.  Can SQL code be used to write a formula?
Crystal Reports

Avatar of undefined
Last Comment
Mike McCracken

8/22/2022 - Mon
bdreed35

To write a formula you need to use either Crystal or Basic syntax.
You can write a formula to convert it.
Can you post the Julian value you have and the date you expect it to be converted to?
Kathy_A

ASKER
It will be all the julian dates held in the table.
bdreed35

Can you post some sample data and the expected outcome?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Kathy_A

ASKER
Here are some julian dates in the table:

58829
57607
58178
58034

Need them in the format of : mm/dd/yyyy

thanks
bdreed35

For at least one of the values above, can you tell me what the date should be?
Kathy_A

ASKER
I don't know.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Kathy_A

ASKER
Jan 24
sept 20
apr 14
nov 21
bdreed35

Is the database that the data is stored in for a certain application?
What database is it?
Kathy_A

ASKER
Clarity
Your help has saved me hundreds of hours of internet surfing.
fblack61
Kathy_A

ASKER
Because the data comes from EPIC it is imaged to Clarity for relational purposes and used in Crystal Reports.  If I could write the sql as an add command that would work as well.

Thanks
bdreed35

This is a really easy formula to write, but I need to know the year for the sample dates you posted.
The number you posted first, is the number of days from a given date.  I need to determine that date to be able to supply you the formula.
So, can you give me the years for the sample date and expected outcome that you supplied?

Number    Expected Outcome
58829      Jan 24
57607      sept 20
58178      apr 14
58034      nov 21

The month and day are useless without the year.
If I get the year, we can then subtract the number of days to get the starting date.
The formula in Crystal would then look like this if the starting date was 1/1/1900

dateadd("d",{table.field},date(1900,1,1))
ASKER CERTIFIED SOLUTION
Mike McCracken

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Kathy_A

ASKER
Thanks to both of you for all your help!!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bdreed35

I appreciate the thanks, but why did you not split the points?
Mike McCracken

Agree.  I simply expanded on bdreed's example.  By the way what is the year?

I can reopen this if you wish

mlmcc
Kathy_A

ASKER
mlmcc, I think this will do I got the idea.

bdreed35, sorry did not know that I could split the points.  My mistake. Maybe you can help with something else so that I can give you the points.

Thanks to both of you!!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Mike McCracken

Kathy_A - For future reference the SPLIT POINTS option is below the last comment.  Hopefully the new interface will make it easier to see or find.

mlmcc