Ms-access – Import VBScript General Date Time Into MS Access Date/Time Column

importms-accessvbscript

Setup

I have a VBScript for driving the stress testing of a web service. The script creates a data file of measurements, with each record timestamped with a general date/time:

FormatDateTime(Now(), 0)

This creates dates like

mm/dd/yyyy hh:mm:ss [AM|PM]

I need to import this data file into a MS-Access 2003 database. The table in the Access database has a column called TimeStamp with a data type of Date/Time and format of General Date.

My import spec has a field named Timestamp with a Date Type of Date/Time. In the Dates, Times, and Numbers frame the following options are selected:

Date Order: MDY
Date Delimiter: /
Time Delimiter: :
Four Digit Years: Checked

The data file is tab delimited with field names in the first row.

Problem

When I import my data file, each record fails on the Timestamp field with a Type Conversion Failure error.

Question

Is there a way to import the data as-is, or do I have to first "massage" the timestamp field into 24-hour date/time format? If the latter, is there a way to format the timestamp in my VBScript code to write the date/time in 24-hour format? I tried

FormatDateTime(Now(), "mm/dd/yyyy hh:mm:ss")

but VBScript barfed at that.

Best Solution

VBScripts FormatDateTime function does not supprot general format strings only a constrained set of constants and 0 is the only choice you have to include both date and time.

Could it be the prescence of the AM/PM that is causing confusion?

You are in a US locale?

Try SetLocale(2057)

This puts the script in to the en-GB (UK) locale. The default date time format is dd/mm/yyyy hh:mm:ss. Its 24 hour perhaps your import would prefer that format.

Related Question