IBM Support

Formatting a Mapics Date field

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.

[{"Product":{"code":"SSTQPQ","label":"Cognos Series 7"},"Business Unit":{"code":"BU002","label":"Business Analytics"},"Component":"Impromptu","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"Impromptu 7.0","Edition":""}]

Historical Number

119414

Document Information

Modified date:
15 June 2018

UID

swg21333308