SQLite timestamp problem

[jjeffman]

Hello,

I am a little bit new at SQLite.

I have created a SQLite table as follows:

Code: Select all

Create table IF NOT EXISTS EVENTO (
	EVENTO INTEGER NOT NULL  PRIMARY KEY ,
	DATAHORA Timestamp(3,3) NOT NULL ,
	MODULO INTEGER, 
	UNIDADE INTEGER	NOT NULL, 
	TIPO_EVENTO INTEGER NOT NULL, 
) ;

I have inserted a record with the values 1,'',1,1,10 respectively.
I have filled up the SQL property of a TZReadOnlyQuery with the text below:

Code: Select all

SELECT evt.EVENTO 
FROM EVENTO evt
 WHERE evt.DATAHORA = :dthr 
 AND evt.UNIDADE = :unid 
  and evt.TIPO_EVENTO = :tpevt 

I could not find any way of passing the DTHR parameter to get the record I have created.
I have tried to pass it as DateTime and also as a String but both options did not retrieve the record.

What am I doing wrong ?

Thank you very much.

[mmvisual]

Set this parameter AsFloat and not AsDateTime, then it works good.
SQLite is a simple database and cannot decode all DateTime formats from all counties.

[jjeffman]

Thank you for answering. I will give it a try.

I have made some changes which are giving me the proper result.

I have eliminated the date comparison, so I have got more than one row. The Locate method works fine passing the value to search for as DateTime.

I am afraid it is not a SQLite problem because the previous SQL query works perfect on SQLite Expert passing the parameters as strings.

Kind regards

[EgonHugeist]

jjeffman,

issue resolved?

[jjeffman]

I am still using the locate method, which works fine.

I have found a clue of the problem. I have defined the field "DATAHORA"as timestamp, and there is not this kind of data type on SQLite. I need to store timestamp values, so I have changed the column type to DATETIME. It might be solved. I will check. I had some troubles with date and time comparison also using Oracle queries and the solution was to use Oracle functions to convert string to timestamp and bind parameters as string. I think I can do the same with TZQuery. The problem, I guess, is linked to localization. When you bind parameters as DATETIME the values are converted to datetime strings according to local datetime string and SQLite do not recognize Brazilian datetime formats.

As a matter of fact I can not mark the issue as solved.

Thank you very much.

Kind regards

[jjeffman]

Hello,

I have changed the SQL query to:

Code: Select all

SELECT evt.EVENTO 
FROM EVENTO evt 
 WHERE strftime("%Y-%m-%d %H:%M:%f",evt.DATAHORA,'localtime') = strftime("%Y-%m-%d %H:%M:%f", :dthr ,'localtime')
 AND evt.UNIDADE = :unid 
  and evt.TIPO_EVENTO = :tpevt 

Now I am binding the "dthr" parameter value as a full timestamp formated string and I am getting the proper result.

Kind regards.