how to use datetimepicker on Delphi 7

Question

How to use TDateTimePicker with time format (hour:minute:second)?
I tried this code:

Sql.text:= ('select * from namatabel where namafield between '+quotedstr(formatdatetime('yyyy/mm/dd', datetimepicker1.date))+' and '+quotedstr(formatdatetime('yyyy/mm/dd', datetimepicker2.date))+' and field order by field ASC');

Also this code:

Sql.text:= ('select * from tablename where fieldname between '+quotedstr(formatdatetime('yyyy/mm/dd hh:mm:ss', datetimepicker1.date))+' and '+quotedstr(formatdatetime('yyyy/mm/dd hh:mm:ss', datetimepicker2.date))+' and fieldname order by fieldname ASC');

Also this code:

Sql.text:= ('select * from tablename where fieldname between '+quotedstr(formatdatetime('yyyy/mm/dd hh:mm:ss', datetimepicker1.datetime))+' and '+quotedstr(formatdatetime('yyyy/mm/dd hh:mm:ss', datetimepicker2.datetime))+' and fieldname order by fieldname ASC');

but those wont work, help me to correct them.
I'm using SQLYog against a MySQL database.

UPDATE

@Jens Borrisholt i try this code before, but it doesnt work either

SQL.Add('SELECT meteran.kd_meter as no,kamar.nama,meteran.waktu,meteran.meter '+
        'FROM meteran,kamar WHERE kamar.idkamar = meteran.idkamar AND meteran.waktu BETWEEN :tgl1 and :tgl2 and waktu group by waktu asc');
ParamByName('tgl1').AsString:=FormatDateTime('yyyy-mm-dd',DateTimePicker1.DateTime);
ParamByName('tgl2').AsString:=FormatDateTime('yyyy-mm-dd',DateTimePicker2.DateTime); end;
DBGrid2.DataSource.DataSet:=MyQuery2;

Learn how to use parameters and you might lose this format hell. Well, you might because of some DBMS' through ADO *complications* as far as I remember (you seem to be using MySQL, and probably ADO ?). — TLama, Aug 31 '15 at 08:55
What is *Sql.text* here, i.e. is that from a Delphi component you are using? What does SqlYog have to do with the question? Please [edit] your question further. — Jan Doggen, Aug 31 '15 at 09:49
ya, i use SQL.text to insert, update, and select database. im not using ADO but i use MyQuery Access. is that different? cuz i am still learning this programming thing. — Wirah Arya Mardjani, Aug 31 '15 at 10:17
Your question is unrelated to date/time picker controls. It would benefit you to understand why. — David Heffernan, Aug 31 '15 at 12:26
Btw, you don't need parentheses around the string value in a Delphi string-assignment statement. — MartynA, Aug 31 '15 at 18:27
@DavidHeffernan is it unrelated? it supposed to be the one filtering between the data, am i correct? — Wirah Arya Mardjani, Aug 31 '15 at 23:13
It's unrelated because a date time from a picker is no different from any other date time. Knowing that would let you isolate the problem. — David Heffernan, Sep 01 '15 at 05:45
@MartynA is telling you that `str := ('foo')` is the same as `str := 'foo'` — David Heffernan, Sep 01 '15 at 05:46

moskito-x · Answer 1 · 2015-09-01T01:51:42.217

Your query is wrong

and fieldname is too much

shortened with data

NOT OK

Sql.text:= 'select * from tablename where fieldname between "2006-06-11"'+
           'and "2006-06-19" and fieldname order by fieldname ASC';

OK

Sql.text:= 'select * from tablename where fieldname between "2006-06-11" '+
           'and "2006-06-19" order by fieldname ASC';

example valid SQL with data

select * from auktionen where auktionende between '2006-06-11' and '2006-06-19' order by auktionende ASC;

All together with your code. without and waktu

BETWEEN :tgl1 and :tgl2 and waktu

MyQuery2.Close;
MyQuery2.SQL.Text := 'SELECT meteran.kd_meter as no,kamar.nama,meteran.waktu,meteran.meter '+
        'FROM meteran,kamar WHERE kamar.idkamar = meteran.idkamar'+
        ' AND meteran.waktu BETWEEN :tgl1 and :tgl2'+
        ' group by waktu';
MyQuery2.ParamByName('tgl1').AsString:=FormatDateTime('yyyy-mm-dd',DateTimePicker1.Date);
MyQuery2.ParamByName('tgl2').AsString:=FormatDateTime('yyyy-mm-dd',DateTimePicker2.Date);
DBGrid2.DataSource := DataSource1;
MyQuery2.Open;

@WirahAryaMardjani : Can you tell me why you have a question checked to be correct, does not mention the problem with the incorrect **and** ? — moskito-x, Sep 01 '15 at 16:13

Binitta Mary · Accepted Answer · 2015-09-01T12:59:41.490

I mt sure what is your exact requirement.ill try to help you.

I)If you want to compare the date of the datetimepicker against a date of datetime field in table,

SQL.Add(format('select * from tablename where cast ([my_date_time_var] as date) between %s and %s ' ,[Quotedstr(FormatDateTime('MM/DD/YYYY',(datepicker1.Date))),Quotedstr(FormatDateTime('MM/DD/YYYY',(datepicker2.Date)))]))

II)If you want to compare the date time of the datetimepicker against a date time of datetime field in the table,

SQL.Add(format('select * from tablename where my_date_time_var between %s and %s ' ,[Quotedstr(FormatDateTime('MM/DD/YYYY HH:MM:SS',(datepicker1.DateTime))),Quotedstr(FormatDateTime('MM/DD/YYYY HH:MM:SS',(datepicker2.DateTime)))]));

III)If you want to compare the time of the datetimepicker against a time of datetime field in table,

SQL.Add(format('select * from tablename where cast ([my_date_time_var] as time) between %s and %s ' ,[Quotedstr(FormatDateTime('HH:MM:SS',(datepicker1.time))),Quotedstr(FormatDateTime('HH:MM:SS',(datepicker2.Time)))]))

Please let me know if your requirement is not the same

wah, thanks! already solved. i put too many 'and' on the code. thanks for letting me know @Binitta Mary. — Wirah Arya Mardjani, Sep 01 '15 at 14:02
you have errors in your SQL : **1.)** `'select * from tablename where cast ([my_date_time_var] as time) ...'` will not work in MySql. **2.)** `where .... between %s and %s ' , ... (FormatDateTime('MM/DD/YYYY HH:MM:SS',(....)...` will also not work. — moskito-x, Sep 01 '15 at 16:16
Sorry I dont know that you are working with MySQL...I gave you MSSQL query as example.I hope the formating will work for you. — Binitta Mary, Sep 02 '15 at 05:19

how to use datetimepicker on Delphi 7

2 Answers2