12-10-2012 07:42 AM
I have written a simple Vi to write data to a SQL database. When adding the timestamp, and converting it to a datetime string using the "Format Datetime String" Vi in the Database Connectivity Toolkit, the output doesn't contain fractional seconds. Since this application will ultimately involve sensors that are sampling at rates much higher that 1 hertz, it is important that the SQL database contains a millisecond value in its timestamp. I have also tried to modify the "Format Datetime String" Vi to include fractional seconds when unbundling by name and then formatting and appending it to the end of the datetime string. However, in this case I get the following error when writing the timestamp to a SQL table:
Error -2147352567 occurred at NI_Database_API.lvlib:DB Tools Create Parameterized Query.vi->NI_Database_API.lvlib:DB Tools Insert Data.vi->Insert Data.vi
I have attached both Vi's mentioned in this post, both the one that I am using to write data to a SQL table and the "Format Datetime String" that I modified to include fractional seconds.
12-10-2012 08:31 AM
Going in to this, there is one thing that you need to know ahead of time: Dealing with time in a database can be a pain!
Ok, with that out of my system, there are a couple things to look for. First, you need to be sure that the DBMS and the datatype you are using support millisecond accuracy - some don't and some only do under certain circumstances. I believe that this is where your problem lies. According to the comments in your code you are using an "Access" database. The problem is that the data/time formatting functions that Jet (the underlying DBMS) provides can not work with fractional seconds.
If you are stuck with this database, you will have to provide some sort of alternative mechanism for saving the milliseconds. You can put them into a separate field or save the timestamp as a string that includes the milliseconds (the ISO-8601 standard is a good option). For example, the timestamp for when I am writing this would be "2012-12-10 08:23:53.819"
The better choice however, is to use a different DBMS that supports time and date values properly. There are a variety of open source products available as well as free versions of SQL Server and Oracle. In fact, depending upon the tools you installed when you setup LV, you may already have the free version of SQL Server installed already.
Mike...
12-10-2012 08:48 AM
Mike, thanks for the response. I am currently using SQL Server Express as my DBMS with Microsoft OLE DE Provider for SQL Server, although I understand the confusion that could arise from the comments in my code.
I have tried to format a SQL Server compatible datetime string that contains millisecond values by modifying the "DB Format Datetime String.Vi", but the "DB Tools Insert Data.Vi" returns an error. Would you mind checking out the attached Vi titled "Format Datetime String with Millisecond Value" to see if anything jumps out at you?
Charlie
12-10-2012 08:54 AM
These are only guesses as I don't have or use the DCT, and goodness knows what it expects inside it, but...
Why does the string have "\01date/time\01" at the beginning of it? Also, shouldn't the delimiter for the milliseconds be a period?
Mike...
PS: when you have time you might want to look over this thread.
05-24-2021 09:32 PM
For anyone looking at this question, the Database Connectivity toolkit is based on Microsoft ADO which does not like timestamps with fractional seconds.