[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Julian Date to SQL Date conversion

Posted on 2008-11-07
20
Medium Priority
?
1,234 Views
Last Modified: 2012-08-13
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"
0
Comment
Question by:yatinganesh
  • 9
  • 5
  • 3
  • +2
20 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 22903642
have you tried this:

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

Expert Comment

by:Kevin Cross
ID: 22903743
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22903771
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22903797
mwvisa1 said:
>>Some numbers work, just not yours.

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

Expert Comment

by:brad2575
ID: 22904199
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 Comment

by:yatinganesh
ID: 22927741
None of the solution provided worked.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22927748
Can you answer where the negative number above comes from?
0
 

Author Comment

by:yatinganesh
ID: 22927940
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22929078
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 Comment

by:yatinganesh
ID: 22929480
Julian date "-27444" should give output as "4/1/2008"
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22932958
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
 

Author Comment

by:yatinganesh
ID: 22937222
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22939126
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22939146
I just ran this in Excel and got 04/03/2008 so your calculation is correct OR they left something out of formula.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22939172
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
 

Author Comment

by:yatinganesh
ID: 22948464
It is giving the same result as my SQL script and not as expected.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22949557
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22949583
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22949648
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 1500 total points
ID: 22973105
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

810 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question