# Julian Date to SQL Date conversion

I tried other answers provided on the forum but nothing giving the output as expected.
Julian date "-27444" should give output as "4/1/08"
###### Who is Participating?

Love these curly date converters... and how misinformed we sometimes are, for example, see http://www.nr.com/julian.html

So you are not dealing with Julian dates per se, but rather a julian representation of unix dates from some epoch - namely sql zero date + 70 years = 1970 = unix epoch - which is why we are add 70 to the "magic number" (which is really just number of days) .

Now, that 65535 question - it is easy really.... 2 bytes can hold numbers between -2^15 and +2^15 - 1, using 15 of the 16 bits for the data and one bit for the sign. If you ignore the sign and use that 16th bit for data, the range of possible values more than doubles. Instead of a maximum 2-byte value of 32,767, the value is 65,535.  However, unix wont, so we need to convert the negatives back to correct digits (ie make it effectively ignore the sign).

So the above posting is fully explained (hopefully) by spelling out in more detail what the component bits are :

declare @days_since_unix_epoch int
declare @2_bit_number_fix int
declare @years_from_1900 int
declare @years_from_1970 int
declare @days_from_start_of_year int

set @days_since_unix_epoch = -27444
set @2_bit_number_fix = case when @days_since_unix_epoch < 0 then 65535 + @days_since_unix_epoch else @days_since_unix_epoch end
set @years_from_1900 = @2_bit_number_fix / 1000  -- ie days are in thousands of years...
set @days_from_start_of_year = @2_bit_number_fix % 1000  -- ie days are in thousands of years, so it is the remainder we need...
set @years_from_1970 = @2_bit_number_fix / 1000 + 70 -- ie days are in thousands of years... then add 70 years
select floor(@years_from_1970 * 365.25 + 0.5) + @days_from_start_of_year - 1 as number_of_days  -- we do -1 because 1st of year is there by rounding up of 0.5
select dateadd(dd, floor(@years_from_1970 * 365.25 + 0.5) + @days_from_start_of_year - 1 , '01 Jan 1900')

interesting stuff really...

AND, if you want REAL LIVE julian date convert to gregorian calendars, with step by step calculations of individual components back to native julian constants... then...
``````Gregorian calendar from Julian day number
Let J be the Julian day number from which we want to compute the date components.
With J, compute a relative Julian day number j from a Gregorian epoch starting on March 1, 4800 (i.e. March 1, 4801 BC in the proleptic Gregorian Calendar), the beginning of the Gregorian quadricentennial 32,044 days before the epoch of the Julian Period.
With j, compute the number g of Gregorian quadricentennial cycles elapsed (there are exactly 146,097 days per cycle) since the epoch; subtract the days for this number of cycles, it leaves dg days since the beginning of the current cycle.
With dg, compute the number c (from 0 to 4) of Gregorian centennial cycles (there are exactly 36,524 days per Gregorian centennial cycle) elapsed since the beginning of the current Gregorian quadricentennial cycle, number reduced to a maximum of 3 (this reduction occurs for the last day of a leap centennial year where c would be 4 if it were not reduced); subtract the number of days for this number of Gregorian centennial cycles, it leaves dc days since the beginning of a Gregorian century.
With dc, compute the number b (from 0 to 24) of Julian quadrennial cycles (there are exactly 1,461 days in 4 years, except for the last cycle which may be incomplete by 1 day) since the beginning of the Gregorian century; subtract the number of days for this number of Julian cycles, it leaves db days in the Gregorian century.
With db, compute the number a (from 0 to 4) of Roman annual cycles (there are exactly 365 days per Roman annual cycle) since the beginning of the Julian quadrennial cycle, number reduced to a maximum of 3 (this reduction occurs for the leap day, if any, where a would be 4 if it were not reduced); subtract the number of days for this number of annual cycles, it leaves da days in the Julian year (that begins on March 1).
Convert the four components g, c, b, a into the number y of years since the epoch, by summing their values weighted by the number of years that each component represents (respectively 400 years, 100 years, 4 years, and 1 year).
With da, compute the number m (from 0 to 11) of months since March (there are exactly 153 days per 5-month cycle; however, these 5-month cycles are offset by 2 months within the year, i.e. the cycles start in May, and so the year starts with an initial fixed number of days on March 1, the month can be computed from this cycle by a Euclidian division by 5); subtract the number of days for this number of months (using the formula above), it leaves d days past since the beginning of the month.
The Gregorian date (Y, M, D) can then be deduced by simple shifts from (y, m, d).
*/

declare @j int
declare @g int
declare @dg int
declare @c int
declare @dc int
declare @b int
declare @db int
declare @a int
declare @da int
declare @y int
declare @m int
declare @d int

set @J  = 2454558 -- Julian day number (rounded up)
set @j  = 2454558 + 32044
set @g  = @j / 146097
set @dg = @j % 146097
set @c  = (@dg / 36524 + 1) * 3 / 4
set @dc = @dg - @c * 36524
set @b  = @dc / 1461
set @db = @dc % 1461
set @a  = (@db / 365 + 1) * 3 / 4
set @da = @db - @a * 365
set @y  = @g * 400 + @c * 100 + @b * 4 + @a
set @m  = (@da * 5 + 308) / 153 - 2
set @d  = @da - (@m + 4) * 153 / 5 + 122
set @Y  = @y - 4800 + (@m + 2) / 12
set @M  = (@m + 2) % 12 + 1
set @D  = @d + 1.5

select @d,@m,@y
``````
0

Commented:
have you tried this:

CONVERT(varchar(10), JulianDateHere, 101)
0

Chief Technology OfficerCommented:
Database Journal Article: "Convert Julian Date to Standard Date.. MSSQL Function"
Author: Anand Mahendra

SQL Sample Script:
http://www.databasejournal.com/img/JulianDate_To_StandardDate.sql
0

Chief Technology OfficerCommented:
I haven't gotten it to work, but figure it may still show you what he was attempting to convert to get to standard date.  Some numbers work, just not yours.
0

Commented:
mwvisa1 said:
>>Some numbers work, just not yours.

Indeed.  I am particularly curious about the negative number :)
0

Commented:
he may be doing a data add to get a date diff and gets a negative number.   In that case the convert wont work as it is just a difference between 2 dates and not an actual date.
0

Author Commented:
None of the solution provided worked.
0

Chief Technology OfficerCommented:
Can you answer where the negative number above comes from?
0

Author Commented:
See its existing system which holds this data (ISAM database). I am working on migration project to transfer the data to SQL 2005. Client has given me the example for conversion - Julian date "-27444" should give output as "4/1/08". I can give more examples of data in the existing system as below which I need to convert.
-30501
-30496
29319
-30494
25069
28190
0

Commented:
yatinganesh,

Sorry, but you are going to have to give mre detail than that.

Does -27444 correspond to 1 Apr 1908, or 2008?  If the former, that implies that the "base date" for the numbering
is 22 May 1983; if the latter, 22 May 2083.  Those dates are just so arbitrary, though, that neither makes sense.

Regards,

Patrick
0

Author Commented:
Julian date "-27444" should give output as "4/1/2008"
0

Commented:
yatinganesh,

With respect, I have seen a couple of different ways to define a "Julian date", and none of them are remotely
like what you posted here.

Regards,

Patrick
0

Author Commented:
I am with additional information -
We have received a excel formula for conversion - =FLOOR(FLOOR(IF(D2<0,D2+65536,D2)/1000+70,1)*365.25+0.75,1)+MOD(IF(D2<0,D2+65536,D2),1000)
which perfectly converts the above example in excel sheet. I have written respective query in SQL, but giving different result -

---------------------------------------------------------------
DECLARE @D2 INT
SET @d2= -27444
DECLARE @date DATETIME

IF @D2<0
SET @date=FLOOR(FLOOR((@D2 + 65536)/1000+70)*365.25+0.75) + (@D2 + 65536)%1000
ELSE
SET @date=FLOOR(FLOOR(@D2/1000+70)*365.25+0.75) + @D2%1000

SELECT @date

------------------------------------------

Its giving output as 2008-04-03. What is missing in SQL script?

0

Chief Technology OfficerCommented:
It appears to be the last part which is adding the MOD value to date.
``````DECLARE @D2 INT
SET @D2= -27444
DECLARE @intVal INT
DECLARE @date DATETIME

--FLOOR(FLOOR(IF(D2<0,D2+65536,D2)/1000+70,1)*365.25+0.75,1)+MOD(IF(D2<0,D2+65536,D2),1000)
SET @D2 = @D2 + CASE WHEN @D2 < 0 THEN 65536 ELSE 0 END
SET @intVal = FLOOR(@D2/1000 + 70) ; PRINT(CAST(@intVal AS Datetime))
SET @intVal = @intVal * 365.25 ; PRINT(CAST(@intVal AS Datetime))
SET @intVal = FLOOR(@intVal + 0.75) ; PRINT(CAST(@intVal AS Datetime))
SET @intVal = @intVal + (@D2 % 1000) ; PRINT(CAST(@intVal AS Datetime))

SET @date = @intVal

SELECT @date
``````
0

Chief Technology OfficerCommented:
I just ran this in Excel and got 04/03/2008 so your calculation is correct OR they left something out of formula.
0

Chief Technology OfficerCommented:
Therefore in SQL you can simply do this:
``````DECLARE @D2 INT
SET @D2= -27444
DECLARE @date DATETIME

--FLOOR(FLOOR(IF(D2<0,D2+65536,D2)/1000+70,1)*365.25+0.75,1)+MOD(IF(D2<0,D2+65536,D2),1000)
SET @D2 = @D2 + CASE WHEN @D2 < 0 THEN 65536 ELSE 0 END
-- SQL does integer division so first floor not needed
SET @date = FLOOR((@D2/1000 + 70) * 365.25 + 0.75) + (@D2 % 1000)
-- Above is the same result as this
--SET @date = FLOOR(FLOOR(@D2/1000 + 70) * 365.25 + 0.75) + (@D2 % 1000)
SELECT @date
``````
0

Author Commented:
It is giving the same result as my SQL script and not as expected.
0

Chief Technology OfficerCommented:
Did you see my post above indicating that I get 04/03/2008 in Excel as well.  Try pasting the formula into Excel and put -27444 in cell D2 and you will see that you have been misinformed as the result is NOT 04/01/2008 even in Excel.
0

Chief Technology OfficerCommented:
Actually, let me be more accurate in my response.  The result of this in Excel is 39539.  If you cast that to its date value in SQL, this is 04/03/2008.

What you are seeing if this cell is formatted as a date is a difference in the starting date for the datetime data type between Excel and SQL.
0

Chief Technology OfficerCommented:
Excel Date - 1 == Jan 1 1900
SQL Date - 0 == Jan 1 1900

This works for your initial case, but you will have to see if I adjusted the one in the correct places.  Since the 65536 was adding to the date to offset it I lowered that by 1 and then subtracted 1 at final step.  One of the other numbers like 70 may be the candidate to change.  You can determine that with the person who wrote the Excel version or through testing your other values if they don't fit my adjustment.
``````DECLARE @D2 INT
SET @D2= -27444
DECLARE @date DATETIME

--FLOOR(FLOOR(IF(D2<0,D2+65536,D2)/1000+70,1)*365.25+0.75,1)+MOD(IF(D2<0,D2+65536,D2),1000)
SET @D2 = @D2 + CASE WHEN @D2 < 0 THEN 65535 ELSE 0 END
-- SQL does integer division so first floor not needed
SET @date = FLOOR((@D2/1000 + 70) * 365.25 + 0.75) + (@D2 % 1000) - 1
-- Above is the same result as this
--SET @date = FLOOR(FLOOR(@D2/1000 + 70) * 365.25 + 0.75) + (@D2 % 1000)
SELECT @date
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.