1. Thomas Goertler
  2. Valentina Database ADK
  3. Freitag, Dezember 18 2015, 01:19 PM
  4.  Abonnieren via E-Mail
Hi,

today I've a question about the WHERE part in a select query.

We've a table with some date fields in there and want to select
records between dataA and dateB.

Currently we use:

SELECT ... WHERE col_date BETWEEN DATETIME'18.12.2015 00:00:00' AND DATETIME'18.12.2015 23:59:59'


On some client installations we're getting an error like:

"Date-time value "18.12.2015 23:59:59" is invalid. Illegal symbol at "3" position.


As far as I understand this means that the "." is wrong and some other char is expected.
This also works for the most of our clients but some get this strange error.

Our project currently uses version 5.8.8 and connects to a local db (no VServer).

Is there a way to track down the problem?

The db settings are: dateformat = "dmY" and the seperator is "." .

Is it maybe possible to use something like that:

SELECT ... WHERE col_date BETWEEN '18.12.2015 00:00:00' AND '18.12.2015 23:59:59'


Is it more safe to use the DATETIME before the a and b values of BETWEEN ?

At the moment we cannot upgrade to version 6 of valentina as we're working on that
for the new year. (subscription is valid).

Thank you in advance.

Regards,
Thomas
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
Try with an SQLDate.

Regards

Beatrix Willius
Kommentar
There are no comments made yet.
Thomas Goertler Akzeptierte Antwort
Hi Beatrix,

thanks for your reply.

I forgot to mention that our project uses the .NET version of ADK and the final query is created via a .NET string. So any idea how to format the .NET date correctly so it works with valentina?

Regards,
Thomas
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
Hi Thomas,

Check please VDatabase.DateFormat

This property is NOT stored on disk. Your app can and should specify it on start of work with some DB.

When your code specify Date Format, then following code can KNOW what format to use, and this not depend on any computer settings, Your code can be safe on any computer.
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
aha, sorry for fast answer ... you point that you use that and DateSep ...
Then it is strange - we need to think.

This is not related to BETWEEN with sure.
The same effect you should see e.g. for WHERE x = ....


Well, (after thinking...) are you sure that your settings of DateFormat and DateSep still alive?
May be you somehow close/reopen db and they are lost?

You can check these settings just before your SELECT command to see what they are?

If they different, then check if you close/reopen db?
Again these settings are NOT saved to disk.
Kommentar
There are no comments made yet.
  • Seite :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 0 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories
Omegabundle
  1. 0 subcategories