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)