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 Comments4 Solutions1177 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~
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 4 Answers and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros