Import XML Date and Time Values Correctly into Microsoft Access
Question details
The user needs Microsoft Access to import XML date and time values without losing the time or changing the date.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Importing XML records containing date and time values such as 02/11/1997 15:57:27 into an Access table.
- Observed behavior
- Imported values show midnight or a zero date instead of the expected date and time.
Open the XML file in a text editor and confirm whether the source uses month/day/year or day/month/year. If you are importing into an existing Access table, open it in Design View and check that the destination field uses the Date/Time data type.
Use unambiguous XML date and time values before importing
Use this approach when regional date formats cause Access to misinterpret dates or lose the time portion during XML import.
A value such as 02/11/1997 is ambiguous: it can mean February 11 or November 2. Use a year-month-day value with a complete time component so that the intended date is clear.
The reported fix used a value such as <DateTimeStamp>2020-01-24 03:25:43</DateTimeStamp>. If the XML is validated against an XSD schema that declares the element as xs:dateTime, use the standard T separator instead: <DateTimeStamp>2020-01-24T03:25:43</DateTimeStamp>.
Open the XML file in a text editor and find the affected date element. Confirm the source convention before converting 02/11/1997: use 1997-02-11 for February 11 or 1997-11-02 for November 2.
Edit the XML values or update the exporting application to write year-month-day dates with hours, minutes, and seconds. For example, write <DateTimeStamp>1997-02-11 15:57:27</DateTimeStamp>; use 1997-02-11T15:57:27 if the element is defined as xs:dateTime.
In Access, select External Data > New Data Source > From File > XML File. In older versions, select External Data > XML File. Browse to the corrected file and import a small test set into a new table before repeating the full import.
Open the imported table in Design View and confirm that the field has the Date/Time data type. Select the field and set its Format property to yyyy-mm-dd hh:nn:ss, then switch to Datasheet View to check both the date and time.

Review Exported Date and Time Data in WPS Spreadsheets
WPS Office is a free, lightweight alternative for everyday document and spreadsheet work, with a familiar interface and Microsoft Office format compatibility for easy migration. Use WPS Spreadsheets to review an Excel or CSV export of your Access data; it does not replace Access or fix its XML import process.

Frequently Asked Questions
Why does Access misread a date such as 02/11/1997?
The value is ambiguous between month/day/year and day/month/year conventions. Confirm the source convention, then change the XML value to a year-month-day form such as 1997-02-11 or 1997-11-02 before importing it.
Should my XML date and time use a space or a T separator?
The reported fix used a space, as in 2020-01-24 03:25:43. If an XSD schema defines the element as xs:dateTime, use the standard form 2020-01-24T03:25:43. Check the XML schema or export settings before choosing the format.
How can I tell whether the time is missing or just hidden?
Open the Access table in Design View, select the Date/Time field, and set its Format property to yyyy-mm-dd hh:nn:ss. Return to Datasheet View. If the expected time appears, it was hidden by the display format; if it still shows 00:00:00, check the source XML and import result.
Why does an imported value show December 30, 1899?
For an Access Date/Time field, December 30, 1899 at midnight represents the numeric value zero. Inspect the corresponding XML value and destination field type, then test the import again with an explicit year-month-day date and complete time.
Can WPS Office fix an Access XML import?
No. The import must be corrected in the source XML and Microsoft Access. To review the resulting data in WPS Spreadsheets, export the Access table through External Data > Excel or Text File, then open the exported Excel or CSV file in WPS.




