Date in ADO multilanguage

[Harvey]

I use ADO--Toolkit for the connection between labview and database access.

But I have trouble converting dates read in labview data. because in every country the date is different

'1/30/2008 1:23:16 PM' Converted into '00:00 DD/MM/YYYY'

'30/01/2008 13.23.16' Converted into '13.23.16,000 30/01/2008'

There is a tool to determine date format in the operative system or ADO date format? Or automatic converter?

Other problem is with the numbers point or comma

'100,00' Converted into '10000'

[Rolf Kalbermatter]

QUOTE(Harvey @ Jan 31 2008, 04:20 AM)

I use ADO--Toolkit for the connection between labview and database access.

But I have trouble converting dates read in labview data. because in every country the date is different

'1/30/2008 1:23:16 PM' Converted into '00:00 DD/MM/YYYY'

'30/01/2008 13.23.16' Converted into '13.23.16,000 30/01/2008'

There is a tool to determine date format in the operative system or ADO date format? Or automatic converter?

You sort of could write one, by converting a carefully selected timestamp into a string and then searching the specific positions and separation characters. The problem however is that this will never be able to solve all problems. This is because the DB engine and the according ODBC or ADO connector can or can not use the local date/time format on the machine they are running. I do know that the MS ODBC/ADO connector always observe the local format of the client machine and the SQL server can be configured to do that or not. Other SQL database engines will most likely have their own ideas about if and where to adhere to local format settings.

There is supposed to be an ODBC standard format for date strings, but I had at least with SQL Server trouble to get that working between different computers. The solution in that case was to use the SQL Server CONVERT() function in the SQL statements to tell it explicitedly the string format the date was in.

QUOTE

Other problem is with the numbers point or comma

'100,00' Converted into '10000'

Same issue really. All MS ODBC/ADO client connectors will recognize the local decimal point. So for them you would use the standard LabVIEW formatting functions making sure "use local decimal format" is enabled in the Options, or using the Format Into String nodes add a %; to the beginning of the format string to tell it to use the local decimal fomat. Other database client libraries however may or may not observe the local decimal format.

Rolf Kalbemratter

[Ton Plomp]

QUOTE(Harvey @ Jan 31 2008, 10:20 AM)

I use ADO--Toolkit for the connection between labview and database access.

But I have trouble converting dates read in labview data. because in every country the date is different

'1/30/2008 1:23:16 PM' Converted into '00:00 DD/MM/YYYY'

'30/01/2008 13.23.16' Converted into '13.23.16,000 30/01/2008'

There is a tool to determine date format in the operative system or ADO date format? Or automatic converter?

Other problem is with the numbers point or comma

'100,00' Converted into '10000'

The default computer timeformat is '%c', to get the local decimal sign use '%;', but you should be aware that a user on a SQL server can have different settings. So it is quite a challenge to build a data parser that gets all the correct info.

Ton

[i2dx]

The ADO Toolkit makes usage of the local computers time format, determined by '%c'.

There are 2 polymorphic Conversion VIs in the ADO-Tool Subpalette, which help you to do the correct conversion. BTW: AFAIK it's the best solution to use always a dot as the decimal separator, because SQL separates the columns by a comma.

Use the "Data to SQL String" VI to convert Data into an SQL string and "Variant to Data" to convert the data from the result into LabVIEW Data types. Both VIs are polymorphic. You have to select the instance manually at the "Variant to Data" vi, because a Variant can be any type.