Search code examples
mysqldelphidatedatetimesqlyog

how to use datetimepicker on Delphi 7

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;
Solution

  • 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