F08042 History Date to JDE Date Conversion?

DBohner-(db)

Legendary Poster
Howdy,

In the HR History table, the Date Terminated is stored as a string, in the F08042.JWHSTD column.

Basically, instead of being store as a date, it is stored in the same format as it would appear - if you looked at the value from the database:
109222
111021
...

BUT - the values are stored as STRING, not DATE and have to be converted....

I need to take that database formatted string, and convert it to a JDE Date - and I'm just not on enough medication (or I'm not finding the right keywords or I'm just lazy)...

Does someone have a simple means to convert a string-based Julian date to JDE Date?

The sun is out, I have a window, it's Friday - and I don't want to play computer geek anymore...

(db)
 

BOster

Legendary Poster
It looks like it is a JDE Date value as stored in the database but saved as a string????? Ugh.

If thats the case, here is some very old Java code I wrote many moons ago that you can port to NER or C to create a conversion function if you like. A quick search didn't turn up any pristine function for doing this.

The key part of the code below is the yr and days variables. I believe days will be the number of days since the start of the year. Example: yr==2015 and days==35 would be jdDate==Feb 4, 2015 (I think - this is off the top of my head).

Code:
	public static Date getDate(int julianDate) {
		DateFormat fmtDt = DateFormat.getDateInstance(DateFormat.SHORT);
		GregorianCalendar cal = new GregorianCalendar();
		
		int yr = (julianDate / 1000) + 1900;
		int days = julianDate % 1000;
		
		try {
			cal.setTime(fmtDt.parse("1/1/" + yr));
		} catch(ParseException e) { e.printStackTrace(); }
		cal.add(GregorianCalendar.DAY_OF_YEAR, days - 1);
		return cal.getTime();
	}
 

TimPierce

Reputable Poster
If you have the Julian dates in Excel you could use this formula to convert to Gregorian.

=DATE(YEAR("01/01/"&TEXT(1900+INT(C5/1000),0)),MONTH("01/01/"&TEXT(1900+INT(C5/1000),0)),DAY("01/01/"&TEXT(1900+INT(C5/1000),0)))+MOD(C5,1000)-1
 
Discover how to build no-code data integrations and business process automations.

DBohner-(db)

Legendary Poster
JMR - Thank you (Bingo Card is a Winner)....

00006 VA evt_DateTerm_HSDT = rtrim(ltrim([VA evt_DateTerm_HSDT],' '),' ' )
00007 -ConvertStringToMathNumeric(B0000580.ConvertStringToMathNumeric)
VA rpt_DateTerm_HSDT [HSTD] -> szInputString [AA15]
VA evt_DateTermed_Math01 [MATH01] <- mnOutputMathNumeric [MATH01]
00008 -ConvertjulianDatetojdeDate(B9100001.ConvertjulianDatetojdeDate)
RV HistoryDateTerm <- jdDateToConvert [TRDJ]
VA evt_DateTermed_Math01 [MATH01] -> mnJulianDateInYYYYDDD [CDL]


Done!

Thank you.

(db)
 
Top