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"
yatinganeshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brad2575Commented:
have you tried this:

CONVERT(varchar(10), JulianDateHere, 101)
0
Kevin CrossChief 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
Kevin CrossChief 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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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

Indeed.  I am particularly curious about the negative number :)
0
brad2575Commented:
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
yatinganeshAuthor Commented:
None of the solution provided worked.
0
Kevin CrossChief Technology OfficerCommented:
Can you answer where the negative number above comes from?
0
yatinganeshAuthor 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
Patrick MatthewsCommented:
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
yatinganeshAuthor Commented:
Julian date "-27444" should give output as "4/1/2008"
0
Patrick MatthewsCommented:
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.

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

Regards,

Patrick
0
yatinganeshAuthor 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
Kevin CrossChief 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

Open in new window

0
Kevin CrossChief 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
Kevin CrossChief 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

Open in new window

0
yatinganeshAuthor Commented:
It is giving the same result as my SQL script and not as expected.
0
Kevin CrossChief 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
Kevin CrossChief 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
Kevin CrossChief 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

Open in new window

0
Mark WillsTopic AdvisorCommented:
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.