Date format while writing to database using database toolbox

[pawel]

Hello All

Again a problem with input locales and database tooldbox.
How to write to database using connectivity toolbox in user defined format (not dependent on Windows locales)?
Every time different user is logged on and writes a measurement to database, the dataformat is different depending on the user setting. I would like to define the date format in LabVIEW and use it always.

Thanks in advance
Pawel

[rolfk]

pawel wrote:

> Again a problem with input locales and database tooldbox.
> How to write to database using connectivity toolbox in user defined
> format (not dependent on Windows locales)?
> Every time different user is logged on and writes a measurement to
> database, the dataformat is different depending on the user setting. I
> would like to define the date format in LabVIEW and use it always.

Mmmh. What is the problem? If you use the Format Data & Time function
you can always specify the string which should be created. Or do you
mean something else?

Microsoft, in their inexplainable wisdom did have ODBC drivers and
databases in the past which actually expected the date in the current
system format and not the SQL format. Also the date-time format betw
een
SQL Server and MS Access is not the same eventhough it is from the same
company.

Rolf Kalbermatter

[pawel]

The connectivity toolbox has a special treatment for the date/time column data type. It has a vi, which encapsulate a formated date&time with something like #date/time#.

Anyways, I chanked the format of the string passed to the insert.vi and it is a correct one, like I want YYYY-MM-dd. The problem is that this date apears in database depending on the local setting (in international options in windows).
That is very annoying, since every user has different local setting and it causes a mess, since I can read a date/time column from database only as a string, without prior knowledge, where is year, month and day. That couses a lot of troubles.

I can try to tell the db connectivity toolbox, that the column, which I am writing to is the
string type not a date/time, but I am not sure if I do not get an error. I shall try that.

regards
Pawel

[rolfk]

pawel wrote:

> The connectivity toolbox has a special treatment for the date/time
> column data type. It has a vi, which encapsulate a formated date&time
> with something like #date/time#.
>
> Anyways, I chanked the format of the string passed to the insert.vi
> and it is a correct one, like I want YYYY-MM-dd. The problem is that
> this date apears in database depending on the local setting (in
> international options in windows).

What database and what type of driver are you using? It seems like the
driver is translating this. LabVIEW itself does little in the database
connectivity Toolkit with the actual strings and just passes them to
ADO. If ADO or the underlying OLE-DB provider or ODBC driver feels like
it needs to d
o such a translation it is either a stupid bug, or the
database is storing that information in fact in its own internal format
and the driver can properly translate it back to the local of the
querying computer.

Rolf Kalbermatter

[pawel]

Hello Rolf

I have tried it on two kind: jet4 and ODBC - SQL Server. They are all dependent on local setting. But I agree it must be caused by the driver.

regards
Pawel

[Ben]

Rolf,

You never fail to entertain!

Who says we Enthusiasts do not have a sense of humor.

Ben

[rolfk]

Ben wrote:

> Who says we Enthusiasts do not have a sense of humor.

This one was rather sarcastic than humerous 😉

Rolf Kalbermatter

[Ben]

Sarcasism = Humour

in my book.

Ben