Hi,
this is the column in date_time format:
"2020-04-12 22:13:44.309"
now I want to convert this column to Julian Date
what is the function to use for conversion to Julian date
Hi @Sanober Khan
I had already posted my solution to your earlier question about joining 2 tables (one
with Raw SQL timestamp and the other with Julian date).
As far as this question goes, you can convert the raw sql date to Julian date using this
formula that I came up with using Interpolation of my earlier results (Astronomy) over
a decade and half ago.
2451545 + DATEDIFF ('day', DATE("2000-1-1"), [RawSQL Date] )
Basically, in the above formula 2451545 is the Julian date corresponding to 1st Jan 2000
(which is the date value you see in the above equation). I also tested this with the value in
your example with a Julian date value of 2458878.
The logic is very simple as 2458878 - 2451545 = 7333 days which would be equivalent to
20 years and 28 days.
We had 5 leap years between 1st Jan 2000 and 1st Jan 2020 which adds 5 days
to 20 x 365 thus giving a total of 7305 days and subtracting this from the earlier value of
7333 gives 28 days. This was the difference so the actual date will be 1st Jan 2020 + 28 days
= 29th Jan 2020 (In case this is getting too confusing 😕 , forget this explanation and just use
the earlier formula which does exactly the same thing). Take care and Best wishes !
Sincerely,
Soumitra
