• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

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
 

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