The extracted data is a timestamp column in a microsoft access 2007 database.
As shown below, the variant says "1:46:45"
But the timestamp converted from the variant is 1 milisecond ahead of the variant.
It's now "1:46:44.999".
It is shown below.
How can I make the conversion accurate?
Solved! Go to Solution.
I think what you are seeing here is an 'inaccuracy' in the variant probe display. It will not display the millesecond values for the timestamp and as such appears to be rounding. I have tried to replicate this using my own machine (LV 2011 SP1) however I do not see the rounding behaviour.
- In your case, isn't the .999 more accurate than the rounded value?
- Which version of LabVIEW are you using?
- Could you attach a snippet of the variant timestamp?
In the database, it is stored as "2010-04-16 01:46:45.000" but not "2010-04-16 01:46:44.999".
"Variant To Data" subtracted a milisecond from the variant and returned the subtracted timestamp.
And I use LabVIEW 2009 SP1.
What do you mean by a snippet of the variant timestamp?
To create a VI Snippet of the data I would do something similar to the following:
If you post the snippet, then we can see the data you are using and reproduce the issue. It is also possible to 'round your timestamp' to the nearest value - although you will have to extract some of the information as a string before the conversion.
Test replace "Variant To Data" by "Database Variant To Data".
"DB Variant To Data" displays the same behavior as "Variant To Data" as shown below.
I'm sorry for using a korean version of LabVIEW.
The company gave me no choice but to use the korean version.
I talked on phone with an NI instrument support.
He told me that because access database doesn't store miliseconds in date/time data type, LabVIEW has to approximate the exact timestamp hence substracts 1 milisecond from the original variant.
How do I insert a milisecond value into the date/time variant obtained from microsoft access database and convert it to the exact timestamp?
This is how I solved the millisecond problem with microsoft access.
Because datetime type of microsoft access database has a precision of up to a second,
some datetime records are accurately converted to LabVIEW timestamp, and other datetime records are converted with 1 millisecond deviation(it could be plus or minus) to LabVIEW timestamp.
I didn't want to discriminate between those converted accurately and those done inaccurately since it could lead to errors.
So I did as below.
These are the first 6 columns of recordset.
recorded_time is an "access" datetime column.
year(datetime) returns year as an integer of 4 numbers, month(datetime) returns the month as an integer between 01~12, and so on.
And I created a SubVI that processes the first 6 columns of a recordset and returns a timestamp.
As you can see the fractional second is set to 0 which means .000.
Now every datetime record has .000 at the end, and no datetime record is converted to LabVIEW timestamp inaccurately.