CONVERT(varchar(10), JulianDateHere, 101)

Solved

Posted on 2008-11-07

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"

20 Comments

Author: Anand Mahendra

SQL Sample Script:

http://www.databasejournal

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

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

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**15** Experts available now in Live!