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.
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.
No comments:
Post a Comment