Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Storage and Retrieval of ISO-8601 Format Date/Timestamp Records in Access / SQL Server

Avatar of Tusitala
Tusitala asked on
Microsoft AccessMicrosoft SQL ServerVBA
10 Comments1 Solution1177 ViewsLast Modified:
Hi all,

So, I have an Access BE database with tables containing the following fields:

transaction_dtm
create_user
create_dtm
modify_user
modify_dtm

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:

ISO-8601.png
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.

VBA_Date.png
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?

TIA
~Tala~