Your date format integration issue requires a comprehensive three-part solution:
1. Date Format Conversion:
ADP’s workforce planning module in adp-2022.2 uses multiple date formats depending on the field and how the data was entered:
- Shift times:
YYYY-MM-DD HH:mm:ss (space-separated, no timezone)
- Planning dates:
YYYY-MM-DD (date only)
- Created/modified timestamps:
YYYY-MM-DDTHH:mm:ss.sssZ (ISO 8601 with milliseconds)
- Custom date fields:
MM/DD/YYYY (US format)
Your target system expects ISO 8601 format (YYYY-MM-DDTHH:mm:ssZ) for all datetime fields. Here’s a robust conversion function:
function convertToISO8601(dateValue, fieldType, defaultTimezone = 'UTC') {
if (!dateValue) return null;
const formats = [
'YYYY-MM-DD HH:mm:ss',
'YYYY-MM-DDTHH:mm:ss.SSSZ',
'YYYY-MM-DD',
'MM/DD/YYYY',
'MM/DD/YYYY HH:mm:ss'
];
let parsed = moment.tz(dateValue, formats, true, defaultTimezone);
if (!parsed.isValid()) {
throw new Error(`Invalid date: ${dateValue}`);
}
return parsed.toISOString();
}
Key considerations:
- Use strict parsing (the
true parameter) to avoid false matches
- Apply default timezone (UTC) when timezone info is missing
- Handle date-only fields by setting time to 00:00:00
- Validate the parsed result before conversion
2. API Payload Compliance:
Your middleware must ensure the transformed payload meets your scheduling system’s requirements:
a) Field-level compliance:
const fieldTransformers = {
shiftStartTime: (value) => convertToISO8601(value, 'datetime', 'America/New_York'),
shiftEndTime: (value) => convertToISO8601(value, 'datetime', 'America/New_York'),
planningDate: (value) => convertToISO8601(value, 'date', 'UTC'),
createdAt: (value) => value, // Already ISO 8601
customDate: (value) => convertToISO8601(value, 'date', 'UTC')
};
function transformPayload(adpData) {
const transformed = {...adpData};
Object.keys(fieldTransformers).forEach(field => {
if (transformed[field]) {
transformed[field] = fieldTransformers[field](transformed[field]);
}
});
return transformed;
}
b) Timezone handling: ADP workforce planning typically uses the employee’s work location timezone. Your scheduling system might use a different timezone. Establish a clear timezone conversion strategy:
- Store the work location timezone from ADP
- Convert all times to UTC in your middleware
- Let the scheduling system convert from UTC to its display timezone
c) DST handling: For shift times during DST transitions:
function validateDSTTransition(datetime, timezone) {
const parsed = moment.tz(datetime, timezone);
// Check if time doesn't exist (spring forward)
if (!parsed.isDST() && parsed.add(1, 'hour').isDST()) {
// Time falls in the "lost hour"
return parsed.add(1, 'hour'); // Move to valid time
}
return parsed;
}
d) Validation before sending:
function validatePayload(payload) {
const errors = [];
// Validate all date fields are ISO 8601
const dateFields = ['shiftStartTime', 'shiftEndTime', 'planningDate'];
dateFields.forEach(field => {
if (payload[field] && !isISO8601(payload[field])) {
errors.push(`${field} is not ISO 8601 format`);
}
});
// Validate shift times are logical
if (payload.shiftStartTime && payload.shiftEndTime) {
const start = moment(payload.shiftStartTime);
const end = moment(payload.shiftEndTime);
if (end.isBefore(start)) {
errors.push('Shift end time is before start time');
}
}
return errors;
}
3. Middleware Mapping:
Implement a comprehensive mapping layer that handles all data transformations:
a) Schema-based mapping: Define a mapping schema that specifies how each field should be transformed:
const mappingSchema = {
workforcePlanning: {
fields: {
shiftStartTime: {
source: 'shift_start',
type: 'datetime',
timezone: 'location.timezone',
required: true
},
shiftEndTime: {
source: 'shift_end',
type: 'datetime',
timezone: 'location.timezone',
required: true
},
employeeId: {
source: 'employee.id',
type: 'string',
required: true
}
}
}
};
b) Automatic field detection: Scan the payload for date-like strings and attempt to convert them:
function autoConvertDates(obj) {
const datePattern = /\d{4}-\d{2}-\d{2}|\d{2}\/\d{2}\/\d{4}/;
Object.keys(obj).forEach(key => {
if (typeof obj[key] === 'string' && datePattern.test(obj[key])) {
try {
obj[key] = convertToISO8601(obj[key]);
} catch (e) {
// Not a valid date, leave as is
}
} else if (typeof obj[key] === 'object' && obj[key] !== null) {
autoConvertDates(obj[key]); // Recursive for nested objects
}
});
}
c) Error handling and logging: Log all date conversions for debugging:
function logConversion(field, original, converted) {
logger.debug({
field,
original,
converted,
originalFormat: detectFormat(original),
timestamp: new Date().toISOString()
});
}
Complete integration flow:
- Receive workforce planning data from ADP
- Log raw payload for debugging
- Extract timezone information from employee/location data
- Apply field-specific transformations using mapping schema
- Auto-convert any remaining date-like strings
- Validate DST transitions and adjust if necessary
- Validate entire payload against target system schema
- If validation fails, log errors and send alert
- If validation passes, send to scheduling system
- Log successful transformations with before/after values
Testing strategy:
Create test cases for all date format variations:
- Dates during DST transitions
- Dates with and without timezone information
- Date-only fields vs datetime fields
- Custom date formats from different ADP modules
- Invalid dates that should be rejected
This comprehensive approach will handle all date format variations from ADP workforce planning and ensure reliable integration with your scheduling system.