Data Import & Export

Import XML Date and Time Values Correctly into Microsoft Access

Steven ParkerSteven Parker Nov 13, 2026 868 views

Question details

The user needs Microsoft Access to import XML date and time values without losing the time or changing the date.

Import XML Date and Time Values Correctly into Microsoft Access
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.
Before you start

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.

Solution 1Recommended

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>.

1
Confirm the intended source date

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.

2
Normalize the XML values

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.

3
Import a test set into Access

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.

4
Verify the stored date and time

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.

Use unambiguous XML date and time values before importing
What the zero date means: Access Date/Time values use a numeric representation. A value of zero corresponds to December 30, 1899 at 00:00:00. Seeing this date indicates a zero value, but does not by itself identify why the import produced it.
Free Microsoft Office alternative

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.

Review exported date and time columns before sharing or further processing.Supports Microsoft Excel .xlsx and .xls formats for exchanging spreadsheet data.Provides a familiar spreadsheet interface for an easy transition from Excel.Offers a lightweight, free option for working with exported tables.
microsoft office alternative - wps office

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.