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?
ASKER
It will be all the julian dates held in the table.
Can you post some sample data and the expected outcome?
ASKER
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?
ASKER
I don't know.
ASKER
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?
ASKER
Clarity
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
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},
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to both of you for all your help!!
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
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!!
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!!
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?