Infor M3 Date Format: User Setting, API and Database

Fix a date format problem

1. 1Decide which layer has the problem: display in the client, value passed to an MI API, or value read from a table.
2. 2For display, open the user record in MNS150 and check the date format setting, then start a new session.
3. 3For an API call, send dates as 8 digits, YYYYMMDD, with no separators, regardless of the user's display format.
4. 4For database reads, treat date columns as numbers in YYYYMMDD and convert in your query or reporting tool.
5. 5Validate that month and day are real. M3 rejects an impossible date such as month 13, and a zero date means not set.
6. 6Check time zones separately. M3 time fields are typically HHMMSS numbers, and conversion between system and user time is a different concern.
7. 7Add a unit test in your integration that round-trips a known date.

Converting in integrations

// JavaScript: M3 YMD8 <-> Date

function toM3(d) { return d.getFullYear() * 10000 + (d.getMonth() + 1) * 100 + d.getDate(); }

function fromM3(n) { var s = String(n); return new Date(+s.slice(0, 4), +s.slice(4, 6) - 1, +s.slice(6, 8)); }

-- SQL Server pattern

SELECT CONVERT(date, CAST(OACUDT AS char(8)), 112) FROM OXHEAD;

Common pitfalls

• !Passing a display-formatted date to an API.
• !Treating 0 as a real date and getting year 0 results.
• !Mixing user display format with stored values when debugging.
• !Forgetting that a changed user format needs a new session.
• !Converting through a string with the server locale, which breaks on another machine.