Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
codyraptor
Resolver I
Resolver I

7digit Julian to Date provides incorrect date

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

2 ACCEPTED SOLUTIONS

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.

View solution in original post

Awesome!  Thanks @Ron 

View solution in original post

3 REPLIES 3
codyraptor
Resolver I
Resolver I

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.

Awesome!  Thanks @Ron 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors