Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!!
Solved! Go to 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.
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?
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.