Does anyone know of a way to change a SQL View of JDE data to translate their date in to a normal date format i.e. 09/05/01. Or if there's a .udf available to convert it within Cognos? We are trying to avoid a long nested "IF" Statement each time the date is pulled into Cognos. The JDE "date" format isn't stored in SQL as a date, but as a numeric field, 9 digits
JDE Format:
CYYddd
C - Century
YY - YY
Ddd - day of year
So:
Century - 0 = 1900
Century - 1 = 2000
YY = 01, 02, 67, 95, etc.
DDD = 1-365 to represent the day.
We are trying to find the best way to convert it in order to pull it into Cognos Impromptu.
JDE Format:
CYYddd
C - Century
YY - YY
Ddd - day of year
So:
Century - 0 = 1900
Century - 1 = 2000
YY = 01, 02, 67, 95, etc.
DDD = 1-365 to represent the day.
We are trying to find the best way to convert it in order to pull it into Cognos Impromptu.