How Can I Coerce LabVIEW into using the SQL/ODBC Date Format?

[amj26]

I'm using LabVIEW 6.0 with the SQL toolkit (spring 2001 distribution) and wish to read a date from an SQL database and then record it to a second one.

As other people have noticed, LabVIEW appears to insist on returning the date string in the locale's prefered format. This isn't much use as there isn't any function - that I've found, at least - for converting between the locale format and any other and I'd prefer my program to function correctly irrespective of the locale settings.

On June 21st a question entitled "Setting Windows Short Time and Date Formats from LabVIEW" was posted and David Boyde replied with a VI to set the locale date and time formats to the yyyy-mm-dd hh:nn:ss fo
rm required. I've tried using this with my program but it appears to alter the long term default setting in the registry without influencing the form used by the currently running process. Indeed, as far as I can tell, LabVIEW needs to be restarted after this VI is called in order for it to notice the change.

Basically, I'm wondering if anyone has managed to figure out a robust and portable method for getting dates from a database in any standard format (I don't mind which one as it's reasonably straightforward to convert between known formats, my biggest problem is that it's impossible to know what format the date will be given in)?

It strikes me that it would be much preferable to do so without altering any configuration settings anywhere if at all possible, and certainly without making permanent changes to them.

Thanks a lot,

Adam Johansen

[JellyBeans]

It really varies from DB to DB. I'm using access and when i try to read back the date time column, this is what i get back.

"8/8/2000 1:13:33 PM"

Which is very straight forward and if i want to insert that back into another table, i can format the correct string so that it goes thru the query. Don't know what your database is returning. I think (although not sure) that SQL Server 7/2000 databases return the same way.

A Rafiq
National Intruments

[megmond]

This is actually a very dangerous date format, as you can not distinguish between US and European date formats this way if the day number is below 13. The USA generaly uses m/d/y, Europe uses d/m/y. This is why I personally always specify dates as y/m/d.

[amj26]

I'm also using Access, actually and I've found that the format it returns is controlled entirely by the windows locale settings in force when LabVIEW starts. This was initially dd/mm/yyyyy hh:mm:ss xM but it can quite easily be changed via the control panel settings to any other form so it REALLY DOES vary between DBs / Platforms / Specific instances and as such there's nothing that can be relied on with regard to the format of the date. THAT's the problem!

[DavidBoyd]

You've described the problem exactly as I encountered it - A. Rafiq's post does miss the point on this. I'm dismayed to hear that the 'SetLocaleInfo' stuff I wrote and posted last June doesn't work for you - though since then I've realized that other factors may influence how it works. I'm pretty sure from the MSDN docs I have that only the current execution thread gets its locale picture changed from it's creator's default - so perhaps the DLL calls at the bottom of the VIs I wrote, as far as 'reentrant' vs. 'UI thread' setting, affect whether it works. Then too, I wrote these and only ever tried them under LV5.1 - perhaps LV6 manages the API calls differently...
I was in Austin at NIWeek and specifically asked Crystal Drumheller, the lead on t
he new DB Connectivity Toolkit, about this issue. It's my understanding from her that since the new toolkit returns date/time fields (indeed all field types, I suppose) via ActiveX variants, that the conversion to string/locale issues are a thing of the past. I have a late beta release of the DBC toolkit - but confess to having not explored this thoroughly. It only runs under LV6, which I didn't upgrade to until recently.

I'll be the first to agree with you that all this mucking around with stuff that has potential side effects on the system-wide locale, registry, etc., is quite distasteful. It did seem to solve my particular issues at the time (last fall), but perhaps I only fooled myself. I'm pretty sure I tested it at the time and saw that the running LV app changed formats around without requiring a restart. If the new DBC is not a viable option for you, perhaps you can give me a holler if you want to pursue the SetLocaleInfo stuff further. I generally pay more attention
to Info-LabVIEW than to comp.lang.labview and this gateway to it. But I noticed this thread and will check it again over the next few days.

Best,
Dave

[megmond]

I am not 100% sure about the problem, but perhaps you can use LabVIEW's locale specific function to create a dummy date string. This will allow you to find out what information is stored where.

For example, use day=1, month=2, year=3, hour=4, minute=5, second=6 to create a locale specific date/time string in LabVIEW. Then you can get a string like: "2/1/3 4:5:6" (USA format) or "1/2/3 4:5:6" (European format).

Choosing the numbers carefully can also give you info on the use of AM/PM and 2 or 4 digit years (e.g. if you use 16 for the hour, and it returns a '4' somewhere in the string, which you don't use for other numbers you can assume it uses AM/PM notation).

As long as you can assume the format returned from the DB uses the same locale specific setting as LabVIEW uses
, you should be able to use this method to determine the date format reliably, and to be able to convert the string from the DB to proper date/time information.

If you can determine the format to INPUT to the database (or use a generic format for that), perhaps you can enter this dummy date into the database and then check the string you receive when querying it back out? Of course this requires write access rights to the DB...

Hope this helps!