How to insert an unambiguous datetime

Question

I need to import a csv file to my access that contains a datetime column in format dd/mm/yyyy hh:mm:ss

Here is what I'm currently doing

DoCmd.TransferText acImportDelim, "my_specification", "temp_table", filePath, True

updateQuery = "Update my_table " _
            & "INNER JOIN " _
            & "(SELECT field1, field2, field_date " _
            & "FROM temp_table) " _
            & "ON my_table.field1 = temp_table.field1 AND my_table.field2 = temp_table.field2  " _
            & "SET my_table.field_date = temp_table.field_date " _
            & "WHERE my_table.field_date IS NULL"

I use TransferText to store the csv file content temporarily into a table. "my_specification" is used to specify the date time column format as dd/mm/yyyy hh:mm:ss.

Then I use an update query to update the date time field from the temp table.

However, problem here is temp_table.field_date format will vary according to user's system locale setting. If my user has mm/dd/yyyy as their system setting, it creates a wrong update in my_table.

To describe better the problem, take date 2014 Aug 4 as an example. In CSV file, it is stored as 04/08/2014 12:12:12. Then it is imported the same into my temp_table field_date column.

The factor of user's locale setting comes in when I update my_table. If my user sets system date format as dd/mm/yyyy, then it can be read correctly as 2014 Aug 4. However, if my user sets system date otherwise as mm/dd/yyyy, then the date will be read as 2014 Apr 08.

Is there anyway I can enforce an unambiguous date time format for this update?

I'm using MS Access 2013, but I hope the database can be compatible with Access 2003 as well.

Thank you.

Can you confirm this: I assume based on your comments that temp_table.field_date is a TEXT column. It always contains date in your specified format, i.e. `dd/mm/yyyy hh:mm:ss`. my_table.field_date is a DATE column. Update statement fails on the computers with American date settings. Am I right in all my assumptions? If not, please update your question to include the details of the columns data types — cha, Aug 04 '14 at 04:50
Problem is similar to http://stackoverflow.com/questions/16661737/how-to-convert-text-column-to-datetime-in-sql and this solution should work. — Dale M, Aug 04 '14 at 05:00
@DaleM: no, it is not similar at all. The solution is for SQL Server. How can you use convert function in Access? — cha, Aug 04 '14 at 05:14
@cha the original field in csv file can be a TEXT, but after my TransferText function with Specification, it is already converted to datetime format in temp_table.field_date — got2nosth, Aug 04 '14 at 06:58
@DaleM I don't need to convert the date value because it is already datetime when I import to temp_table. However, ambiguity arises when I try to read it and update the value to my_table — got2nosth, Aug 04 '14 at 06:59

Renaud Bompuis · Answer 1 · 2014-08-04T08:56:41.570

It's not completely clear if the CSV file you get from your users already has the date in the proper format or not.

In any case, the only way to solve this is for you to first have the right information from the users.
Either:

You make the users export the date in an unambiguous format, like the ISO 8601 date format yyyy/mm/dd hh:mm:ss
Or you know, from each of your users, what is their locale date format and create multiple specifications for importing the CSV file into your temp_table (you need to select the right specification for each data import).

If you can't do either of these, meaning you just get a CSV file and you don't know where it comes from then you can't easily solve your problem.

One way to "guess" the format of the CSV file is to have a dummy field in the CSV file with a date with a high day, like 24DEC2014, for instance in the first record.
With some code, you can then check the format of the date of that dummy record and find out if it's stored in 24/12/2014, or 12/24/2014, or even 2014/12/24. Depending on that you can switch specification when you import the CSV file.
Of course, this is only possible if you have some control over the way the users produce the CSV file.

If the CSV file contains lots of data for wach day of the month, then you may be able to detect their date format by scanning for the way each date is written and checking, for instance, if the date is valid for a given format.
This can only work if you are sure to always have enough data in the CSV file to cover at least a one record with a day 13 or above.

Basically, it all comes down to what control you have over the CSV file or at least what assumptions you can make about its data.

I have no control over the csv file or the user's locale setting. But the csv date format is fixed as dd/mm/yyyy hh:mm:ss. As I described earlier on, there is no problem importing the date into my temp_table. But the problem arises when I update my_table with datetime values from temp_table — got2nosth, Aug 04 '14 at 08:17
Something doesn't make sense. If your users are indeed creating the CSV file according to the proper date format, then their locale doesn't matter and then importing the data into `my_table` should work. Are you the only one importing the data or are your users also importing the data in the Access app on their own computer? — Renaud Bompuis, Aug 04 '14 at 08:56
I never mentioned about creating csv file by my user... the CSV file is transferred from a different system which has a fixed format dd/mm/yyyy — got2nosth, Aug 05 '14 at 00:48

How to insert an unambiguous datetime

1 Answers1