1. Thomas Goertler
  2. Valentina Database ADK
  3. Friday, December 18 2015, 01:19 PM
  4.  Subscribe via email
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
Comment
There are no comments made yet.
Beatrix Willius Accepted Answer
Try with an SQLDate.

Regards

Beatrix Willius
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 1
Thomas Goertler Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 2
Ruslan Zasukhin Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 3
Ruslan Zasukhin Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 4
  • Page :
  • 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. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories