Converting Julian Dates in SQL Server

[vchai]

Does anyone have a function or something that can help me convert a Gregorian date to a Julian date and vice versa in SQL Server?

 

[DRGJDE]

While not elegant and requiring some overhead, I use these home grown user functions:

Go from JDE date to Gregorian date:
select dbo.jde2greg(rpdgj)as 'Date', rpdgj from proddta.f03b11 where rpdgj = 103307

Date rpdgj
------------------------------------------------------ --------------------
2003-11-03 00:00:00.000 103307
2003-11-03 00:00:00.000 103307
2003-11-03 00:00:00.000 103307
2003-11-03 00:00:00.000 103307
2003-11-03 00:00:00.000 103307

CREATE FUNCTION JDE2GREG (@DATE integer)
RETURNS datetime
AS
BEGIN
DECLARE @JDE2GREG datetime
IF @DATE < 99001
SET @JDE2GREG = NULL
ELSE
SET @JDE2GREG =
CAST('01/01/'+ substring(str(@DATE,6),2,2)as datetime) + (cast(substring(str(@DATE,6),4,3) as integer)-1)
RETURN(@JDE2GREG)
END


To go from Gregorian to JDE:
select dbo.fs_greg2jde('11/02/2003')
-----------
103306
(1 row(s) affected)

CREATE FUNCTION
fs_GREG2JDE2(@FROMDATE CHAR(10) )
RETURNS NUMERIC(6,0)
AS


BEGIN
/* This function calculates the JDE Julian date from a Gregorian string date The input parameter must be mm/dd/yyyy
This routine will not work when the year passes 2999. (big deal)

DGoboff 02/24/2003 */

DECLARE @JDE_FROM NUMERIC(6,0)
SET @JDE_FROM = 0

if substring(@fromdate,1,2) < 1 or substring(@fromdate,1,2) > 12 goto ENDOFCODE


If substring(@fromdate,7,4)>1999
set @jde_from = (datediff(day,'01/01/'+ substring(@fromdate,7,4), @fromdate) +1)+(substring(@fromdate,9,2) * 1000)+100000
else
set @jde_from = (datediff(day,'01/01/'+ substring(@fromdate,7,4), @fromdate) +1)+(substring(@fromdate,9,2)) * 1000

ENDOFCODE:


RETURN(@jde_from)
end

=================================================
If you use this code (or something like it) please remember to test as much as you can. CP&S and I do not stand behind this code. Use it at your own risk.

Let me know if you make any improvements...

dave

(ps-it seems to work fine)

 

[jdesmm]

I'm going to look over your JDE2GREG function, as I think it will work better than my similar function, but I think my ConvertToJulian function might be a bit more efficient than the GREG2JDE, so I'll post it.

/* ConvertToJulian
--------------------------------------------------------
Description: This function was created to convert a standard datetime format to JDE's Julian date format.
Parameters: @inDate - the datetime to convert to Julian, @jdDate - the 6 digit Julian date returned
Usage: Select ConvertToJulian('YourDate') as JulianDate, etc. - rest of Select statement. Recommend using full 4 digit year.
--------------------------------------------------------
*/
CREATE FUNCTION ConvertToJulian (@inDate datetime)
RETURNS numeric(18,0)
AS
BEGIN
DECLARE @jdDate numeric(18,0)
SET @jdDate = ((DATEPART(yy, @inDate)-1900) * 1000) + DATEPART(dy, @inDate)
RETURN(@jdDate)
END

 

[ice_cube210]

Hi,

I had a similar requirement that I had to change some dates in table at the back end as some wrong entries had been made.

I had a small software which I got from the net , it converts a Juilan date to a Gregorian date and vice versa

The Link is

http://home.swipnet.se/astro-gregor/pl/downloads.htm

There will be two download links on this page , one for the JD to GRG date converter and one for the GRG Date to Juilan converter.

Suprisingly I found the JD to GRG converter more of use to me .

Now heres what you have to do. Open your table in UTB. Identify the record you want to change.

Now query on the same table and record using query analyzer and see what is the value returned for the date field.

Enter this value in the JD to GRG converter software and check it.

For example a date that is shown in ur UTB as 10/6/2003(mm/dd//yyyy) will have a value 103279 in SQL. Now say you want to change the date to 10/1/2003 that is 1st October instead of 6th October you just have to subtract the no of days from 103279. So 1st October 2003 will be 103279-5 which is 103274.

So Just fire an update statement to append to modify the record. Do find in your UTB again and you can the see that the date would have changed.

This is fine if you have to change just a few records. if you have to change Lot of dates I guess the Functions the others mentioned might be a better option

Note I am on SQL 2000. I dont know if it is different in SQL 7. Check it first in some test environment.