Be seen. Boost your question’s priority for more expert views and faster solutions

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"

Julian date "-27444" should give output as "4/1/08"

Author: Anand Mahendra

SQL Sample Script:

http://www.databasejournal.com/img/JulianDate_To_StandardDate.sql

>>Some numbers work, just not yours.

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

-30501

-30496

29319

-30494

25069

28190

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

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.

I think you need to ask your client for more information.

Regards,

Patrick

We have received a excel formula for conversion - =FLOOR(FLOOR(IF(D2<0,D2+65

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

ELSE

SET @date=FLOOR(FLOOR(@D2/1000

SELECT @date

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

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

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

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

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.

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

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.

All Courses

From novice to tech pro — start learning today.

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

Open in new window