database date-time variant to date-time in seconds, for numeric comparison operations

[trbo_usr]

I am building a user login vi with Labview 8.2 that checks whether stored date/time values in the user record (MS SQL Server Express) have expired.  I retrieve data/time values from the database as variants and use the database variant to data vi wired to a string data type, getting a mm/dd/yyyy hh:mm:ss AM/PM output string.  I can convert this to a numeric date/time for comparison to the current date/time by parsing the string and using the cluster to numeric date/time vi.  In testing of this, sometimes I get off by an hour, due to DST confusion I suppose.  This is not a killer issue.  However, I am concerned about how this will work in other countries, where the variant to data conversion on a date/time value should sometines yield a dd/mm/yyyy etc. string perhaps (?)

Is there a simpler, and internationally robust, way to do this?  Ideally, it would be database platform independent, but for now I am only using SQL Express.

Also, I have seen some forum items on problems with LV 8.2 databasevariant to data vi applied to date-time values. Does 8.2.1 fix this?

Thanks.

[rolfk]

---

@trbo_usr wrote:

I am building a user login vi with Labview 8.2 that checks whether stored date/time values in the user record (MS SQL Server Express) have expired.  I retrieve data/time values from the database as variants and use the database variant to data vi wired to a string data type, getting a mm/dd/yyyy hh:mm:ss AM/PM output string.  I can convert this to a numeric date/time for comparison to the current date/time by parsing the string and using the cluster to numeric date/time vi.  In testing of this, sometimes I get off by an hour, due to DST confusion I suppose.  This is not a killer issue.  However, I am concerned about how this will work in other countries, where the variant to data conversion on a date/time value should sometines yield a dd/mm/yyyy etc. string perhaps (?)

Is there a simpler, and internationally robust, way to do this?  Ideally, it would be database platform independent, but for now I am only using SQL Express.

Also, I have seen some forum items on problems with LV 8.2 databasevariant to data vi applied to date-time values. Does 8.2.1 fix this?

Thanks.

---

Not sure about the Database Toolkit really, but in general the issues are in the driver and not in the interface to it. Most databases support a native binary data retrieval for date/time values but once you retrieve them to a string this is obviously dependant on how they get converted to that string. The most difficult part is that you should really tell the database driver to return a data/time value as such instead of a string. Have you tried to convert the actual variant to a timestamp instead of a string?

I'm not sure if ActiveX allows for explicit date/time datatype (and if the LabVIEW ActiveX to LV Variant conversion does convert that to an internal timestamp but I'm pretty sure it does if ActiveX allows for that datatype). If this seems not to work I'm afraid you  will probably have to abandon the simple "retrieve the whole table as a 2D array of variants" and start to code the table retrieval more explicitedly avoiding the variants at all.

Rolf Kalbermatter

[Ching_F.]

Hello trbo_usr,

I agree with Rolf, there may not be a simple answer to this question.  Take a look at this document which describes how to determine the time zone in LabVIEW.  This may eliminated the time zone issues you are experiencing.

With regards to your question about the Database Variant to Data vi applied to date-time values, this is part of the Database Connectivity Toolset, and is not revised at the same time as LabVIEW.  The issues with this VI that were in LabVIEW 8.20 still exist in LabVIEW 8.2.1 since this toolset has not been upgraded yet.  The workaround for this issue is to use the Variant to Data vi instead of the Database Variant to Data when working with time stamp data.

Please let me know if you have any further questions by replying to this post.  Best of luck on your application, and have a great day!!

[trbo_usr]

Thanks, Rolf and CP-

I'll switch to the Variant to Data vi right away.   I am curious about Rolf's suggestion of skipping the "whole table to 2-D array of variants approach".  I am actually running a stored procedure that returns a single record as the returned recordset, then I pull out one column from this record.  Is there a Labview vi that I can apply to this recordset reference other than the fetch vi in order to return data from the recordset in its native format, by-passing the variant datatype?

Assuming that I stay with the current method, is there an easy way to test country-specific behaviour of the variant to data vi without changing the country setting on my computer?

[Ching_F.]

Hi trbo_usr,

I think that the easiest way to test country-specific behavior of the variant to data vi without changing the country setting is by manually modifying the time stamp.  Take a look at the following image for an example on how to do this.

Please let me know if you have any further questions.

Message Edited by cphuong on 04-18-2007 12:07 PM

[trbo_usr]

Rolf:

I am going to try the conversion to timestamp directly as you suggest.  I think that will be the  way around country-specific differences in generating the date-time string, avoiding transposing days and months.  For variant to timestamp, can I use the "database variant to data" vi, or do I need to use the standard variant conversion vi?

Also, I did not notice a "timestamp - to - date-time in seconds" vi, only the reverse, on the type conversion pallette.  Can you point me to that vi if it exists?

Thanks

[rolfk]

---

@trbo_usr wrote:

Rolf:

I am going to try the conversion to timestamp directly as you suggest.  I think that will be the  way around country-specific differences in generating the date-time string, avoiding transposing days and months.  For variant to timestamp, can I use the "database variant to data" vi, or do I need to use the standard variant conversion vi?

Also, I did not notice a "timestamp - to - date-time in seconds" vi, only the reverse, on the type conversion pallette.  Can you point me to that vi if it exists?

Thanks

---

I have no detailed knowledge about the internals of "Database Variant to Data" versus "Variant to Data". All I know is that "Database Variant to Data" knows how to deal with a NULL ActiveX variant while the normal "Variant to Data" didn't at least at the time Variants got introduced.

Converting a timestamp to seconds is done with the Convert To Double function. Quite logical although not obvious. I had to search too the frist time.

Rolf Kalbermatter

Message Edited by rolfk on 04-20-2007 05:02 PM

[trbo_usr]

For anyone reading this thread still...I continued to struggle with international issues surrounding database date-time retrieval into LabView, and have learned these three things:

1) Storing date-time values to a database should NEVER be done with date-time data types!  Much easier to use Labview to make the conversions to/from simple UTC integers and be assured that it runs the same everywhere.

2) In my case, the toothpaste was out of the tube and I needed to deal with the date-time data type.  In the end I just created a reference record in my database with a known date-time value.  Now I retrieve this record at startup to determine any and all rules for interpreting the local data-time conversions back into data-time strings before I attempt to retrieve unknown values.  For this purpose, always use a "PM" time with all numeric fields (y/d/m/h/m/s) a different value, and preferably single-digit so leading zeroes can be discovered if desired.

 3) In principle there are system settings discoverable that would allow me to bypass the empirical approach and know every aspect of the date-time conversion process (like what text is used for AM/PM, etc.) directly.  So I might try this at some point, but for now I still like testing actual outcomes per 2).

[TinoMK]

I am having similar problem...I working with variant type from ActiveX in labview. What is the appropriate LV type if I know that the format of the data is SafeArry of DATETIME.

I've tried many ways using variant to data then DB variant to data but bo success....

Thanks,

[Tanya_V]

Do you have any documentation for the ActiveX command you are trying to use?

Also what data types have you tried casting the variant data to?