7digit Julian to Date provides incorrect date

[codyraptor]

Need help please.  I have a column filled with Julian dates.  I can use 'To_Date(date, 'j') and get the correct result in SQL without any issues.  However, I need to do this in M to maintain query folding and filter the size of my table for better efficiency.

I've tried the code below and several others...but nothing seems to get me the correct answer.

Example J-Date:  2458851  = 1/2/2020

The code below gives me 4/30/2460

taken from @MarcelBeug 's post...

Date.AddDays(#date(Number.RoundDown([JulianDate]/1000),1,1),Number.Mod([JulianDate],1000)-1)

 Much appreciate any help!!  Thanks!!

[ronrsnfld · Accepted solution]

The code from the blog that you tried is, I believe, using a different definition of Julian date than you are.  Some define Julian date as the Gregorian Date in the format of  yyyyddd  where "ddd" is the day number of the year.  So 2-Jan-2020 would be 2020002 in that system.

See this article on Julian Day Numbers by Peter Meyer for more information than you really need. I would think a simple subtraction should work.

[codyraptor]

Not sure if this is right....but I'm using the following code and it seems to give the correct date.  The hard coded number is the starting point for oracle julian dates.

Date.From([J-Date]-2415018.5))

Any concerns that could cause errors doing it this way?

[ronrsnfld · Accepted solution]

The code from the blog that you tried is, I believe, using a different definition of Julian date than you are.  Some define Julian date as the Gregorian Date in the format of  yyyyddd  where "ddd" is the day number of the year.  So 2-Jan-2020 would be 2020002 in that system.

See this article on Julian Day Numbers by Peter Meyer for more information than you really need. I would think a simple subtraction should work.

[codyraptor · Accepted solution]

Awesome!  Thanks @Ron