cancel
Showing results for
Did you mean:
Helper IV

## Convert Julian Date to Calendar Date

Dear Team,

I am getting the dates as Julian Date with in CSV file ,I parsed this CSV file. now I wanted to convert these dates in to calendar date. ( YYYY-MM-DD). Could you please help me to convert this date so that I will store it in SQL Database.

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
Most Valuable Professional

As far as I know todays Julian date is 2459286.

You could use the following expression in an compose action to calculate an UTC time based on a calculation with ticks.

``addseconds('0001-01-01', int(mul(sub(2459286, 1721425.5), 86400)))``

Btw, found this useful Nasa page with an converter for testing:

https://ssd.jpl.nasa.gov/tc.cgi

And found the calculation on Wikipedia:

https://en.wikipedia.org/wiki/Julian_day

Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #MicrosoftCopilotStudio content?

2 REPLIES 2
Most Valuable Professional

As far as I know todays Julian date is 2459286.

You could use the following expression in an compose action to calculate an UTC time based on a calculation with ticks.

``addseconds('0001-01-01', int(mul(sub(2459286, 1721425.5), 86400)))``

Btw, found this useful Nasa page with an converter for testing:

https://ssd.jpl.nasa.gov/tc.cgi

And found the calculation on Wikipedia:

https://en.wikipedia.org/wiki/Julian_day

Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #MicrosoftCopilotStudio content?

Helper IV

I had tried this solution,  it is perfectly working fine but from the DB level not with any formulas.

CREATE TABLE dbo.JDEDates
(
JDEDate VARCHAR(6),

GregorianDate AS CONVERT(SMALLDATETIME,
100*CONVERT(INT, LEFT(RIGHT('0'+JDEDate,6),1))
+10*CONVERT(INT, SUBSTRING(RIGHT('0'+JDEDate,6), 2,1))
+CONVERT(INT, SUBSTRING(RIGHT('0'+JDEDate,6),3,1)),
0))
) PERSISTED
);

INSERT dbo.JDEDates(JDEDate) SELECT '111186';

SELECT JDEDate, GregorianDate FROM dbo.JDEDates;

Thanks

Announcements