LabVIEW forget the milliseconds from a datetime field in SQL

[lraynal]

I’m exporting a table (Microsoft SQL Server) to a CSV file, and LabVIEW truncate my datetime field forgetting the milliseconds!
Here is the result: 31/03/2005 12:21:01 PM
How can I extract this precious information?
Thanks,

[rolfk]

---

@laurentNVSI wrote:
I’m exporting a table (Microsoft SQL Server) to a CSV file, and LabVIEW truncate my datetime field forgetting the milliseconds!
Here is the result: 31/03/2005 12:21:01 PM
How can I extract this precious information?
Thanks,

---

How do you export thi? What VIs are you using? Basically if you are retrieving data from an SQL table using the Database Toolkit and then dump the data to disk as CSV file all in LabVIEW, you have two possibilities where things could get wrong.

One is you are retrieving everyting as strings and simply dump it. It is unlikely that LabVIEW would remove the precision from the time string so you would have to create your query string to tell MS SQL Server to also return the seconds precision instead. No precision might be the default for MS SQL Server and you might need to explicitedly tell it to return that too.

If you retrieve the data as native datatypes then it can either go bad in the SQL conversion where the precision might get lost or in the data formatting when writing to disk. Just go through those VIs in debug (single step) mode once and look where it might go wrong.

Rolf Kalbermatter

[GabbyG]

Since this thread was never really solved, probably because the original starter got ans answer from elsewhere AND
the question describes exactly my problem I opted to continue here.

I am using LV 8.6.1 with database toolkit to retrieve data from an MS SQL server 2005.
The data base has an item of type "datetime" which I use as a primary key. When writing and reading using the toolkit (e.g. Rec Get Value From Field.vi)
the returned variant already seems not to include the milliseconds. Al least when when "Rec Fetch Datetime Data (R).vi" converts it into a string it is already missing.

Is there a way to force SQL server to report the milliseconds (which it has, I verified) or is there an other Vi in the plethora of DB-Vis that might do it ?

Thanks for your consideration

Gabi

[TLE]

I am not sure if SQL Server is the same, but in MySQL the datetime field does not included ms precision.  To get around this, I created another field in the database and stored the milliseconds there.  Just combine them back together when you retrieve the data.

[rolfk]

The native ODBC value to store timestamps is TIMESTAMP_STRUCT and does allow for a fraction of seconds which is in ns. If the database does really use that is of course a different question. I'll go and make some tests in the next days with SQL Server and my own ODBC interface library to see if there is at all a possibility to retrieve fractional seconds for timestamps.

[rolfk]

Well, I think I can solve the mystery. datatime has no fractional seconds. You need a timestamp field for that.

[rolfk]

Sorry for the noise so far. Timestamp in SQL Server is not the solution unfortunately. I have no poblems retrieving fractional seconds for datetime fields using my ODBC library but the ADO interface in LabVIEW seems not to support that easily. The only way I could get the fractional seconds was to convert the returned variant directly into a timestamp.

[GabbyG]

I have a workaround now. But thanks for your answers.

1. MySQL really seems to have no fractional seconds in it's Datetime, what a pity. I am reworking an old program, that had originally the LV Timestamp as Float. Surly precise enough, but how terrible to check in the table (like with SQL management studio or another viewer).

2. With the above mentioned MS Tool I  could edit the timestamp directly in the database to check the up- and down- path separately.  
LV uses variants to transfer data from and to the DB. On the from DB Side this seems to work.
 For the ToDB direction  I bypass the problem now by converting the LV Timestanp to string. This needs a modification in the NI-database-toolkit.
In the CMD Create Parameters.vi the DB Tools_IsDateTimeString.vi is used.  This I modified so that a Timestamp at input is converted to a ISO Timestamp string, which the SQL interface accepts without problem.
It's just that LV (on a German personalized Windows) does not allow a modification of the decimal character. So you'll have to exchange the , for . after the convert timestamp.


Maybe this is an issue for NI to work at.
BTW: the "Boolean" definition is also missing in the DB- toolkit


Gabi

[Steffen01]

old topic same question. I send ms values from LV. I used time(3), datetime(3), timestamp(3) in MySQL. But the time is always just seconds. Ala LV time is 12:34:56.7890 but in MySQL I just get 12:34:56.000

If I make MySQL to generate the timestamp, it actually makes 12:34:56.789

The ms value is not really important, but if I log in 1000ms intervals sometimes it happens its less than 1000 ms between logs and it could mean two timestamps are identical. As in 12:34:56.001 and 12:34:56.999 make as timestamp 12:34:56.000  and then the program terminates. I checked, "with wait 1000 ms" it may log every 1000.1 ms but with wait until next multiple of 1000ms it actually logged every 997 or so ms which really caused the program to terminate after a few steps.

1000.1 ms is not bad, it means every 10000 s one s gets missing. But this is just the test program, once I log 140 values it may take way more than 1000.1 ms between iterations.