Problem querying date/time fields

Problem querying date/time fields

Postby GEswin » Wed 23 Mar 2005 16:52

Code: Select all
DBQbsclliure.SQL.Clear;
DBQbsclliure.SQL.Add('select distinct(fecha) from doctores_agenda');
DBQbsclliure.SQL.Add('where codigo_doctor = :coddoc and fecha >= :fecha');
DBQbsclliure.SQL.Add('and hora > :hora1 and hora < :hora2 and nombre is null');
DBQbsclliure.SQL.Add('order by fecha');
DBQbsclliure.ParamByName('hora1').AsTime := EncodeTime(00,01,0,0);
DBQbsclliure.ParamByName('hora2').AsTime := EncodeTime(23,59,0,0);
DBQbsclliure.ParamByName('fecha').AsDate := cxDateNavigator1.Date;


Code: Select all
select distinct(fecha) from doctores_agenda
where codigo_doctor = 4 and fecha >= "2005-03-23"
and hora > "00:01:00" and hora < "23:59:00" and nombre is null
order by fecha


With this code I get no results from the query, but if I run this query from mysql, or hardcode date/time into query (2 code part) it works. Looks like the parameters are wrong converted or similar.

hora is a time field, fecha is a date field.
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby Ikar » Fri 25 Mar 2005 13:50

We couldn't reproduce the problem.
Please send us (mydac*crlab*com) complete small sample to demonstrate it and include script to create and fill doctores_agenda.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby kenny » Sat 26 Mar 2005 09:36

Hi,
Is this possible of the line
DBQbsclliure.ParamByName('fecha').AsDate := cxDateNavigator1.Date


Where the dateformat of cxDateNavigator is not yyyy-mm-dd? Please format it as "yyyy-mm-dd" :wink:
kenny
 
Posts: 43
Joined: Mon 15 Nov 2004 08:48
Location: Malaysia

Postby GEswin » Sat 26 Mar 2005 19:27

Where the dateformat of cxDateNavigator is not yyyy-mm-dd? Please format it as "yyyy-mm-dd"


Kenny, as I'm passing it as a date, the components set the proper format.

We couldn't reproduce the problem.
Please send us (mydac*crlab*com) complete small sample to demonstrate it and include script to create and fill doctores_agenda.


Ikar, I will prepare a demo tomorrow and send it to support.
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby GEswin » Mon 28 Mar 2005 10:27

Sorry Ikar, my fault.. I was just completing email with database dump etc and then I discovered that I didn't fill the parameter :coddoc , so with reason there was never a result.

regards
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain


Return to MySQL Data Access Components