I am importing data from a CSV file into an Access table and comparing them to data imported into a second Access table from a second CSV file.
This all works perfectly in the latter part of the month. However in the first part of the month I get varying results. Before the 13th of the month data is imported as mm/dd/yyyy whilst after this date it is imported as dd/mm/yyyy.
e.g on 20th Jan the date appears as 2016-01-20 in the csv file and imports as 20/01/2016 whereas on the 6th Jan the date appears as 2016-01-06 in the csv file and when imported into the access tables it will import as 06/01/2016 into one but 01/06/2016 into the other.
Both tables are in the same database and are configured the same. Has anybody else come upon this and more importantly could you resolve it?
Dates are ALWAYS defaulted and treated as MM/DD/YYYY in access SQL, no matter what your default international settings are. If you see your dates as DD/MM it is because Access knows that your system locale is DD/MM and presents the dates like that to you, but it wont do that in SQL instructions. For US there's no problem. For EU and mostly the rest of the world there are a lot of problems. When Access inserts a date, it always consider that it is in the US format MM/DD, when it's not, YOU should format the date correctly. that's fine. BUT, when it find a date for which the MM/DD format isn't possible (month greater than 12), it understands that the date isnt MM/DD but rather DD/MM, and do the conversion itself ! This "strategy" is not smart at all from Microsoft and leads to a lot of problems. It is way better to have all dates incorrect or to throw an error, so you can patch directly, rather than have half of your dates correct and the other half incorrect and sometimes notice it months latter.
The solution:
When you make manual INSERTS of dates in your tables, you should do a format(thedate,"MM/DD/YYYY") or use YYYY/MM/DD format which is always recognized and implicitly converted
When you import from a text/CSV file, you should specify that your date format is DMY. You can do that in the import wizard, at step 3 or 4, with the button "advanced" that lies at the bottom.