Bug with date on QueryArray/Value/Row

[emeasoft]

Hello Xailers,

We discovered that there is a bug with dates in SQLite when using the methods QueryValue/Array/Row.

When executing a command, in most cases it returns an empty array if you use a date in it.

We are wondering, if you will release a version to correct this bug, since we can't release a version to our clients with this bug.

TIA,
Juliano

[ignacio]

Please explain the bug. If possible include a small project. TIA.

Regards,

[ignacio]

Hello,

I believe your problem must be related with the fact of how date values are treated internally. If you use the lDateAsString property to .T. the search clause must be done using the expression:

Código: Seleccionar todo

WHERE <cField> > 'YYYY-MM-DD'

But if you have that property to .F. the the correct syntax will be:

Código: Seleccionar todo

WHERE <cField> > Ctod( "dd/mm/yyyy" )

The problem can be worst, if some records are saved with lDateAsString set to true and some others with set to false. Then you MUST convert internally all the date values to text or integer.

In order to do such work easily I have modified our Sqlite editor tool to show with foreground colors the native data type value (integer, double, text, blob, null) so you can easily check how your date values are internally stored. We have also included a new feature to massively change the date and date-time field values to text or integer.

Please download the new file from: http://www.xailer.com/download?en&file=10 and at first change the configuration to see the native field types with foreground colors. Afterwards, check how your date fields are internally stored and then use the new option 'Change native date type values' under the 'Compact database button'.

Regards,

[emeasoft]

Hello Ignacio,

Here we use the property on its default value (.F.), but even using the "WHERE <cField> > Ctod( "dd/mm/yyyy" )" it does not return the right result.

The problem seems to be when comparing between dates, here goes an example.

Just a question, which one is better to use, lDateAsString as false or true?

Regards,
Juliano

[ignacio]

I believe your code is not correct. This:

cSql := ::dSQL:BuildSQLSt("SELECT * FROM TBL WHERE C12>? AND C12<? ", {Date()+30,Date()+60} )

Should be:

cSql := ::dSQL:BuildSQLSt("SELECT * FROM TBL WHERE C12>? AND C12<=? ", {Date()+30,Date()+60} )

Then you get your 2500 value .

BTW, the second Logdebug() here it returns 5000

I also got the same results with or without DB encryption.

About which system is best, dates as strings or dates as numbers, the first is more SQLite compatible, the second uses nearly a third of the space (8 bytes against 23 bytes). Is your decision.

Regards,

[emeasoft]

That's true Ignacio, my fault.

Using the new sqlite editor we noticed that some date fields were being recorded with some other kind of data type but keeping the format (dd-mm-yyyy), after that we added an extra verification when converting the old dbf files and it seems that now the error stopped...

Many thanks!