Converting a JD Edwards Date to a System.DateTime
- by Christopher House
I'm working on moving some data from JD Edwards to a SQL Server database using SSIS and needed to deal with the way in which JDE stores dates. The format is CYYDDD, where:
C = century, 1 for >= 2000 and 0 for < 2000
YY = the last two digits of the year
DDD = the number of the day. Jan 1 = 1, Dec. 31 = 365 (or 366 in a leap year)
The .Net base class library has lots of good support for handling dates, but nothing as specific as the JD Edwards format, so I needed to write a bit of code to translate the JDE format to System.DateTime. The function is below:
public static DateTime FromJdeDate(double jdeDate)
{
DateTime convertedDate = DateTime.MinValue;
if (jdeDate >= 30001 && jdeDate <= 200000)
{
short yearValue = (short)(jdeDate / 1000d + 1900d);
short dayValue = (short)((jdeDate % 1000) - 1);
convertedDate = DateTime.Parse("01/01/" + yearValue.ToString()).AddDays(dayValue);
}
else
{
throw new ArgumentException("The value provided does not represent a valid JDE date", "jdeDate");
}
return convertedDate;
}
I'd love to take credit for this myself, but this is an adaptation of a TSQL UDF that I got from another consultant at the client site.
