Tusitala
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:
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:
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~
So, I have an Access BE database with tables containing the following fields:
transaction_dtm
create_user
create_dtm
modify_user
modify_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:
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~
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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. >>
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~
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")
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hi Vitor,
Thanks for your input. So what datatype do you use when storing dates/times in SQL or Access?
~Tala~
Thanks for your input. So what datatype do you use when storing dates/times in SQL or Access?
~Tala~
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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~
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~
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
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~