So, I have an Access BE database with tables containing the following fields:
In each table containing the above fields, I have set the date as ISO-8601 format using the Access table designer (i.e. Table Design -> Data Type -> Date/Time -> Format -> "yyyy-mm-dd hh:nn:ss AM/PM". All works fine according to my needs.
Now, I am in the process of migrating the BE database to SQL server and have started off using DateTime as the datatype for the above mentioned fields. To support this approach, I have used the following code to create date/timestamp records in ISO-8601 format:
Dim dtmDateTime As String
dtmDateTime = Format(Now(), "yyyy-mm-dd hh:mm:ss AM/PM")
The results in SQL table being:
However, while testing in Access today, I found a couple of problems that I hope will help with answering this question.
The first problem I noticed was the date-time fields displaying in "mm/dd/yyyy" format instead of ISO-8601 format when opening linked SQL tables in datasheet view. As far as I am aware, the reason for this is due to to the regional settings of the machine accessing the Access database? If I open the same database on another machine with UK region settings for instance, the dates are displayed in "dd/mm/yyyy" format instead. Etc..
The second problem I noticed was declarations and needing to convert strings/dates when working with recordsets in Access VBA.
With that said, I am aware that there are some strong arguments/opinions regarding the storage of dates in SQL Server. Adding to this is the issue with DateTime vs DateTime2 datatypes when communicating with Access databases.
So, my question is this - What should I be doing to ensure that my date/timestamp fields in both SQL and Access are working with ISO-8601 format?