CAST Bug

Discussion of open issues, suggestions and bugs regarding Virtual Data Access Components for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

CAST Bug

Post by Aggie85 » Sat 10 Mar 2018 04:19

Howdy Maxim!

I found another issue with TVirtualQuery.

If you have a TDateTime field in a source dataset and issue a SQL statement as follows:

SELECT CAST(DateTimeField AS DATE) AS SomeFieldName FROM SourceDataSet

No matter what the TDateTime date value is, the date is always returned as October 7, 1905.

I have emailed you a test app demoing the issue.

All the best,

Aggie85

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: CAST Bug

Post by MaximG » Wed 14 Mar 2018 13:13

Obviously, this issue is not related to the error in VirtualQuery, but it occurs due to the work specificity of the SQLite DBMS, on the basis of which the SQL engine is built in VirtualDAC. When defining the field types of the query result, SQLite for the CAST expression (DateTimeField AS DATE) returns the INTEGER type. As a result, the date value, which is initially stored in TkbmMemTable in a field of DATETIME type is read as INTEGER, and then converted to TDateTime when displayed in TDateTimeField. This is the result of the wrong displayed value. Unfortunately, we cannot affect the behavior of SQLite in this situation in any way.

Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Re: CAST Bug

Post by Aggie85 » Wed 14 Mar 2018 15:20

Howdy Maxim!

I was actually thinking that yesterday and I haven't GOOGLED it yet. I can fix it in the data set but I will see if I can find a SQLLITE solution later today.

Thanks for all the help.

Aggie85

Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Re: CAST Bug

Post by Aggie85 » Wed 14 Mar 2018 15:49

Howdy All!

IF anyone else ever runs into this issue, see the SQLITE 'strftime' method!

All the best,

Aggie85

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: CAST Bug

Post by MaximG » Fri 16 Mar 2018 08:36

We are glad that you found a necessary solution. You are right, you can use the SQLite-function "strftime()" to solve the described task. Please contact us on any questions regarding the use of our product

Post Reply