Search code examples
delphisqlitedelphi-7zeos

Delphi 7 + Zeos: how to prevent sqlite 3 converting date and integer/float to string?

Test database:

create table a ( d date not null primary key);
create table b ( d date not null primary key);
insert into a values ('2013-01-01');
insert into b values ('2014-01-01');

Using Zeos lib with Delphi 7, these queries all return TDateTimeField:

select d from a order by 1;

select d from b order by 1;

select d from a union all select d from b;

select * from (select d from a union all select d from b) s;

However, this query returns a TStringField:

select * from (select d from a union all select d from b) s order by 1;

Questions:

  • Why is that?
  • How do I prevent this from happening?
  • Is this a bug? How on earth ordering a result set changes the type of a column????
  • This is a serious problem because I cannot generate SQL queries from my program and open them in a TZReadOnlyQuery that was created in design time

Update: it does not work with integers either.

create table c ( id integer not null primary key);
create table d ( id integer not null primary key);
insert into c values(1);
insert into d values(2);

These result in TLargeIntField:

select * from c order by 1

select * from d order by 1

select * from ( select * from c union all select * from d) s

However, this results in a TStringField:

select * from ( select * from c union all select * from d) s order by 1
Solution

  • As stated here:http://www.sqlite.org/datatype3.html

    SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

    TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
    REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
    INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

    So, can you try the statement below and post the results?!

    select date(*) as test from (select date(d) from a union all select date(d) from b) s order by 1;