• Registered: 2017-05-15
  • Posts: 722

Topic: date filter

Hello MVD!

I have a project having a result of union query( 5 connected tables inside of it).  I already achieved the union query and it runs on the query builder "NAVICAT". My problem now is on how to filter it via datepicker thru SQL Script inside the MVD using the button.

I need a help! pls!

mainxs....

  • Registered: 2014-02-14
  • Posts: 10,294

Re: date filter

Hello.


An example for button with action "SQL Query" (dtDateOfBirt is name of DateTimePicker)

SELECT
employees.lastname,
employees.firstname,
employees.dateofbirth

FROM
employees

WHERE

(CASE WHEN {dtDateOfBirth} IS NOT NULL THEN date(employees.dateofbirth) = date({dtDateOfBirth}) ELSE 1=1 END)

Project example:

Post's attachments

Attachment icon SQL Search.zip 9.35 kb, 558 downloads since 2018-03-22 

Dmitry.
  • Registered: 2017-05-15
  • Posts: 722

Re: date filter

Thanks Much MVD! 

Sorry for the late replay...

  • jrga
  • Member
  • Offline
  • From: Brasil
  • Registered: 2015-10-26
  • Posts: 127

Re: date filter

Please, I ask the one who will be able to help and to a desenvolvedor, which gives support for the forum, as I can compare a date he was seeing script. The filter used in the button SEARCH with the operator> = for the least date is lowered by me, IT DOES NOT WORK.

procedure frmComissoes_OnShow (Sender: string; Action: string);
begin
   frmComissoes.dtIni.datetime := strtodate(SQLExecute('SELECT strftime("%d/%m/%Y",MIN(Data_Venda)) FROM Vendas'));
   frmComissoes.dtFim.datetime := strtodate(SQLExecute('SELECT strftime("%d/%m/%Y",MAX(Data_Venda)) FROM Vendas'));
end;

Roberto Alencar
  • Registered: 2014-02-14
  • Posts: 10,294

Re: date filter

Check it out

procedure frmComissoes_OnShow (Sender: string; Action: string);
begin
   frmComissoes.dtIni.datetime := SQLDateTimeToDateTime(SQLExecute('SELECT MIN(Data_Venda) FROM Vendas'));
   frmComissoes.dtFim.datetime := SQLDateTimeToDateTime(SQLExecute('SELECT MAX(Data_Venda) FROM Vendas'));
end;
Dmitry.
  • Registered: 2014-06-25
  • Posts: 863

Re: date filter

DriveSoft wrote:

Check it out

procedure frmComissoes_OnShow (Sender: string; Action: string);
begin
   frmComissoes.dtIni.datetime := SQLDateTimeToDateTime(SQLExecute('SELECT MIN(Data_Venda) FROM Vendas'));
   frmComissoes.dtFim.datetime := SQLDateTimeToDateTime(SQLExecute('SELECT MAX(Data_Venda) FROM Vendas'));
end;

Can somebody explain this please? How do you use this : SQLDateTimeToDateTime?

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,674

Re: date filter

Hi,
SQLite does not have a storage 'type' of date (datetime).  Rather, all dates (datetimes) are stored as 'text' in the format 'yyyy-mm-dd HH:MM:SS:NNNN.
If you are extracting a date from a table using, for example, 'sqlexecute' and placing it into a 'datetimepicker', then 'sqldatetimetodatetime' performs the conversion for you.
Maybe others can give a more detailed explanation.
Regards,
Derek.

  • Registered: 2014-06-25
  • Posts: 863

Re: date filter

Thanks Derek, I have tried that with a datetimepicker but got errors about an incompatible "extended" type, hence my question to understand what it was for!

9 Reply by brian.zaballa (edited by brian.zaballa 2023-10-22 22:56:42)

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: date filter

I think it is better to make a simple test on the problem you encountered (incompatible "extended" type). It is faster to spot the problem in this way.
If the output is can be done by a simple search, then make use of it. making it to script is a last resort for me.

Post's attachments

Attachment icon TestDateTime.zip 549.05 kb, 59 downloads since 2023-10-23 

brian