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

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

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

Here are some julian dates in the table:

58829

57607

58178

58034

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

thanks

58829

57607

58178

58034

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

thanks

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

I don't know.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Jan 24

sept 20

apr 14

nov 21

sept 20

apr 14

nov 21

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

What database is it?

What database is it?

Clarity

Your help has saved me hundreds of hours of internet surfing.

fblack61

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

Thanks

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

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},

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

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

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

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

I can reopen this if you wish

mlmcc

I can reopen this if you wish

mlmcc

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

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

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

mlmcc

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?