Convert Julian Date

khenyu

Member
i want convert the Julian Date into Normal Date.
the field name is (GLDGJ and GLDKJ)

select a.GLKCO as Company,a.GLMCU as BusinessUnit,a.GLOBJ as ObjectAccount,a.GLSUB as Subdiary,a.GLDGJ as GLDate, a.GLEXA as Explain1,a.GLEXR as Explain2, a.GLAA as ActualAmount,a.GLDOC as DocNo,a.GLJELN as JurnalLine,a.GLDKJ as Check_date, a.GLDCT as Doc_type,a.GLAN8 as AddressNum,b.WWMLNM + b.WWMLN1 as name from PRODDTA2.F0911,PRODDTA2.F0111 where GLCO in ('00700','00710') and GLOBJ='XXXX' and GLDGJ >=113001 and a.GLAN8=b.WWMLNM;

Can someone advise me?
 
This is probably the wrong board,should be in OWDEV
TO_DATE(TO_CHAR(GLDGJ+1900000),'YYYYDDD')
TO_DATE(TO_CHAR(GLDKJ+1900000),'YYYYDDD')
 
Hi,
here is the code for iSeries db2, I created SQL-functions for that:
CREATE FUNCTION PRODDTA2.CONVERTDATETOJDEDATE (
DATEVAL DATE )
RETURNS NUMERIC(6, 0)
LANGUAGE SQL
SPECIFIC PRODDTA2.CONVERTDATETOJDEDATE
NOT DETERMINISTIC
CONTAINS SQL
RETURNS NULL ON NULL INPUT
CONCURRENT ACCESS RESOLUTION DEFAULT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN
DECLARE YEARVAL INT ;
DECLARE CENTURY INT ;
DECLARE JDEDT NUMERIC ( 6 , 0 ) ;
SET YEARVAL = YEAR ( DATEVAL ) ;
SET CENTURY = ( YEARVAL - 1900 ) / 100 ;
SET YEARVAL = YEARVAL - ( 1900 + ( CENTURY * 100 ) ) ;
RETURN CENTURY * 100000 + YEARVAL * 1000 + DAYOFYEAR ( DATEVAL ) ;
END ;



CREATE FUNCTION PRODDTA2.CONVERTJDEDATETODATE (
JDEDATE NUMERIC(6, 0) )
RETURNS DATE
LANGUAGE SQL
SPECIFIC PRODDTA2.CONVERTJDEDATETODATE
NOT DETERMINISTIC
CONTAINS SQL
RETURNS NULL ON NULL INPUT
CONCURRENT ACCESS RESOLUTION DEFAULT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN
RETURN DATE ( VARCHAR ( 1900 + INT ( JDEDATE / 1000 ) ) || '-01-01' ) + INT ( RIGHT ( VARCHAR ( JDEDATE ) , 3 ) - 1 ) DAY ;
END ;

Best regards,
Carsten
 
I'm not sure of your database platform. However, we have in the past needed to convert JDE Julian dates to Gregorian (Normal) dates or vice versa. Again I also have a function to convert a Gregorian date to JDE Julian date if needed.

That said I found this SQL Function that we use in SQL Server on the web that works fantastic. Full credit being given to Bryant Avey who published this solution.

Create FUNCTION [dbo].[DateJ2G]
(
@JDEDATE int, @FORMAT int
)
RETURNS varchar(20) AS
--Written by Bryant Avey, InterNuntius, Inc.
--Provided free "As Is" with no warranties or guarantees
--I just ask that you keep these comments in the function, if you use it.
--The complete article describing this function can be found at:
--http://wp.me/pBPqA-a

--This function takes a JDE Julian Date and returns
--a varchar date in the format style you specify
--To us simply pass in the JDE date and the style code
--Style codes can be found at

--For Example: select dbo.DateJ2G(sddgj,101) from f4211
--would return the JDE date in the format of 02/29/2008.
--Select dbo.DateJ2G(108060, 1) = 02/29/08
--Select dbo.DateJ2G(109060, 107) = Mar 01, 2009

--Format codes are standard SQL 2005 Date Convert codes.
--Conversion codes can be found here: http://wp.me/pBPqA-a
BEGIN
DECLARE @sqldate datetime
set @sqldate =
dateadd(day,cast((1900000 + @JDEDATE)%1000 as int)-1,(cast((
cast((1900000 + @JDEDATE)/1000 as varchar(4)) + '-01-01')
as datetime)))

RETURN (convert(varchar(20),@sqldate,@FORMAT))
END

GO
 
try this one:(but make sure the julia date is not zero)

convert(char(10),dateadd(dy,(GLDGJ-floor(GLDGJ*.001)*1000)-1, dateadd(yy,floor(GLDGJ*.001),'01-01-1900')),103)

convert(char(10),dateadd(dy,(GLDKJ-floor(GLDKJ*.001)*1000)-1, dateadd(yy,floor(GLDKJ*.001),'01-01-1900')),103)
 
Back
Top