asked on

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

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")

Open in new window

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?

TIA
~Tala~

SOLUTION

Anders Ebro (Microsoft MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tusitala

ASKER

Thanks for the reply, Anders.

So, putting aside the user display part, what datatype would you use to store dates/timestamps in either Access or SQL? Would you use DateTime, DateTime2 or Integer?

What impact would you see your choice making in a multi-country user base and how would it affect for example, your SQL queries and code logic when dealing with a myriad of typical business planning subjects such as Workforce Labor in hours, minutes and seconds, or Forecasting product demand using historical and future Dates in days or weeks etc..?

~Tala~

Anders Ebro (Microsoft MVP)

If you have the possibility to install a newer driver than the default SQL Driver (which dates back to 2000 or so) then I would use DateTime2(0). If you are forced to use the older driver, then stick to DateTime.

As for general work with dates, if I am passing dates in SQL to SQL server, then I format the date as TEXT in ISO format. If the form is bound to a recordset, then Access will do the date magic just fine on its own, and you don't need to worry about it.

For general work with dates, I strongly recommend using a date table. It can really make things easier once you get used to it.

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tusitala

ASKER

<<If you have the possibility to install a newer driver than the default SQL Driver (which dates back to 2000 or so) then I would use DateTime2(0). If you are forced to use the older driver, then stick to DateTime.>>

So, I have three different drivers and I am currently using the older one because my Access FE is not allowing me to create DSN-less links with the newer driver. Maybe that is a separate issue but I recall reading a few posts on here suggesting to stay well away from using datatype DateTime2 due to issues with Access?

<<As for general work with dates, if I am passing dates in SQL to SQL server, then I format the date as TEXT in ISO format. If the form is bound to a recordset, then Access will do the date magic just fine on its own, and you don't need to worry about it. >>

Dim dtmDateTime As String
dtmDateTime = Format(Now(), "yyyy-mm-dd hh:mm:ss AM/PM")

Open in new window

So, when adding records to SQL in VBA, I am already doing the right thing according to your approach would you agree?

<<For general work with dates, I strongly recommend using a date table. It can really make things easier once you get used to it. >>

Noted.

~Tala~

SOLUTION

Anders Ebro (Microsoft MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tusitala

ASKER

Hi Vitor,

Thanks for your input. So what datatype do you use when storing dates/times in SQL or Access?

~Tala~

Vitor Montalvão

I work only with SQL Server and I store data using datetime, smalldatetime (less accurate - only stores until seconds), date (only date, no time) or time (only time, no date) depends on the requirements. I'm not sure what are the correspondent data types for Access but I think that shouldn't be hard to find with a search engine :)

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tusitala

ASKER

Ok, so thanks for your help Anders, Vitor, and Gustav.

It seems that the general consensus is to use DateTime as opposed to DateTime2 or Integer - at least when an Access database is involved.

So with that said, I hope none of you object to my assignment of points but I felt that Gustav's recommendation was somewhat consistent with the views expressed in similar posts on both EE and the general internet.

TFTH
~Tala~