Troubleshooting
Problem
An Impromptu report accesses a Mapics database. Date data types from this database use a 2 or 3 digit year. For example, 021012 instead of 2002/10/12 and 980801 for 1998/08/01. Impromptu sees the field as a packed decimal column, length 4, precision 7. How can the date be converted to a regular, Gregorian calendar date, so that it can be formatted and date functions can be used against it?
Resolving The Problem
A separate calculated column is needed for year, month and day.
These columns can then be used together to create a new calculated column with the calendar date. Assuming 'Mapics Date' is the column from the database, here are the calculations -
Month: floor ( mod ( Mapics Date, 10000 ) / 100)
Day: mod ( Mapics Date, 100 )
Year: ( floor ( Mapics Date / 10000 ) ) + 1900
Calendar Date: datetime-to-date ( make-datetime ( Year, Month, Day ) )
'Calendar Date' can then be formatted and used in date functions.
Historical Number
119414
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21333308