Date/Time erros when working with Database Connectivity toolkit

[Gurdas]

Hi!,

We are observing errors with date/time when working with LV. The error occurs when the system datetime format is set such that day precedes month (e.g. dd/mm/yyyy). Our VI uses LV 7.1, Database Connectivity 1.0 and SQL Server 2000 on a Win2000 machine.

Problem details
---------------

Our database in SQL server has a table having columns with data type "datetime"

If the system date format (as set in control panel) is "mm/dd/yyyy", the 'insert into database' vi works fine. This function inserts the date and time (among other things) into the above mentioned table.
However, when the system datetime is set to dd/mm/yyyy we start receiving error that date and time is beyond range for dates in which the date had the day field greater than 12 (e.g. 23/10/2005). With the same settings, if the date is such that day field is equal or less than 12 (e.g. 03/10/2005), we do not receive an error but the date is interpreted as 10 March 2005 rather than 03 September 2005.

Clearly LV (or is it SQL) is mistaking the day field as month.

We have taken care that when sending and receiving date, the format date and time string is set as per system settings.
Thus, if system setting is "dd/mm/yyyy" our format string is "%d/%m/%Y"
And if the setting is "mm/dd/yyyy" our format string is"%m/%d/%Y"

Any help on problem cause and cure is welcome.

Thanks,

Gurdas

[Xu]

Hi Gurdas,

From your post, I guess you use "Format Date/Time String" vi to format a time stamp into a string.
Did you try to use "DB Tools Format Datetime Str" vi to convert the time stamp into a string?

Xu

[Gurdas]

Hi Xu,

Thanks for the reply.

I did consider using the "DB Tools Format Datetime Str" string but stopped because of two reasons:

1) I was not convinced it will give any advantage because what we were doing seemed logically correct

2) The number of places where we need to implement the change is high.

But if we are convinced it will help, there will not be any hesitation to implement.

What do you feel? Or maybe we can try the change in a small part of the code and then go for a full change?

Warm wishes,

Gurdas

[Dynamik]

Hi Gurdas,

      The attached VI converts LabVIEW seconds (DBL) to MSAccess time (with no daylight-savings-time correction!)

I don't know if other DBs store Date/Time in the same [numeric] format as MSAccess.

Maybe it's a terrible idea to do this, but (in my case) it completely eliminated string handling.

Cheers

Message Edited by Dynamik on 10-05-2005 12:25 PM

Message Edited by Dynamik on 10-05-2005 12:27 PM

[Xu]

Hi Gurdas,

I used "DB Tools Create Parameterized Query", "DB Tools Set Parameter Value", "DB Tools Format Datetime Str" and "DB Tools Execute Query" VIs to insert datetime data to database.

The default date format for English version SQL Server is MM/DD/YY.
Just setting the system date format to DD/MM/YY will not help in using that date format for SQL Server. You can use Set dataformat statement in SQL Server to change the date format setting.

In your case, my suggestion is to use "DB Tools Format Datetime Str" vi which converts time stamp to ISO date format string (with prefix substring "\01datetime\01") which SQL Server should process correctly.

Hope helps,

Xu

[Xu]

Sorry, "Set dataformat statement" should be "Set Dateformat statement".

[Gurdas]

Hi Dynamik,

Thanks for your reply. I have put in a more detailed reply below (in answer to Xu's idea).

- Gurdas

[Gurdas]

Hi Xu,

You answer led me to some very interesting fact finding on how SQL server handles date and time. I have attached a zip file which contains webpages that throw more light on this issue.

The attached pages tell me that SQL has an inbuilt date/time reference format. The default in mm/dd/yyyy. Which explains why my VI worked when I used that format to write to SQL.

However there is a catch:

SQL expects the date/time to be in its inbuilt reference format when you WRITE data to SQL. If the date/time is in a different format, better tell SQL about it by using say the SET command you mentioned.

BUT what about the date/time format when you are reading data from SQL?
Our finding is that SQL sends date/time string in the system date format when you READ from SQL !!! That is very surprising behaviour (why differentiate between write and read?).

Is our finding correct?

So, we adopted the following simple strategy (yet to be fully tested):

1) Whenever we write date/time to SQL, the string is formatted as mm/dd/yyyy. Presently, the user's SQL server is in the default state. Caveat is that if the user changes SQL date/time from default (which is mm/dd/yyyy) to anything else, our software will give errors. But then he changed it  😉

2) When we read date/time from SQL we format the string as per system date/time format.

I know this is not very robust coding. But assuming the user keeps his SQL in the current setting, should we expect smooth working?

In other words, are there any errors and/or flaws in our strategy?

Thanks,
Gurdas

[Gurdas]

Sorry, forgot to attach the zip file in my previous post .....

[Xu]

Hi Gurdas,

Your solution should smoothly works, though is not very robust coding said (when your user change the SQL date format settings).

My suggestion is to format the date as yyyy-mm-dd (if it does not work, format to yyyymmdd). This will be independent on the SQL settings (more robust). You can use "DB Tools Format Datetime Str" vi (triming extra stuff), or other ways.

Good luck,

Xu