Julian Date and its sql conversion..

Time and again we pull up UPMJs from the tables via backend and need to convert the date that gets pulled into Gregorian. Following query when appended to any date column in a jde table will give the Gregorian date for the corresponding Julian:

TO_CHAR(TO_DATE(XXXXXX+1900000,’YYYYDDD’),’MM-DD-YYYY’)

Notes:
– where XXXXXX is the column name to convert.
– this only works on 19th century onwards.

If you are more comfortable with XLS following are the two conversion formulae that will help:

=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+MID(A1,2,2),1,RIGHT(A1,3)) where A1 is your date in Julian

="1"&RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000") where A1 is your date in gregorian.

SQL to find Out all the active users in the system

Execute the following SQL command to get all the active users with a particular role (ABCD)
select distinct SCUSER NT_ID, ABALPH UserName
from sys7334.F0092 a, sys7334.f98owsec b, devdta.F0101 c
where a.ULUSER = b.SCUSER
and a.ULUSER = c.ABALKY
and a.ULUGRP like ‘%ABCD%’
and b.SCEUSER = ’01’
and c.ABAT1 = ‘E’
and b.SCUSER not in
(‘JDEXYZ’, ‘JDE’, ‘AFPCRPMON’);