how to filter data by date and time from datetime column

You are using strings
the values need to be converted to datetime

SELECT * FROM TABLE  WHERE DATE_START BETWEEN convert(datetimem,:d1) AND convert(datetime,:d2)

it`s the same as before - filtration works on dates but i can change time on datetimepicker3 and 4 without any result.
For example:
I`ve got a record with date= '2011-12-29 13:13:13.000' and if i choose;
d1:= '2011-12-01 14:00:00'
d2:= '2011-12-30 22:00:00'
it shouldn`t be present in result of select - but it is.

Use Qutates "QuotedStr()":

d1:= FormatDateTime('yyyy-MM-dd',DateTimePicker1.DateTime)+' '+FormatDateTime('HH:mm:ss',DateTimePicker3.Time);
d2:= FormatDateTime('yyyy-MM-dd',DateTimePicker2.DateTime)+' '+FormatDateTime('HH:mm:ss',DateTimePicker4.Time);

SELECT * FROM TABLE  WHERE DATE_START BETWEEN :d1 AND :d2

Search.Params.ParamByName('d1').AsString := QuotedStr(d1);
Search.Params.ParamByName('d2').AsString := QuotedStr(d2);

Jimy i`ve got:
"Conversion failed when converting date and/or time from character string."

You need to get the d1 and d2 into proper datetime formats

I like to attack these issues by making it simple.

First hardcode the values into D1 and D2.

once you have done that make the code do the same.

d1:= FormatDateTime('yyyy-MM-dd',DateTimePicker1.DateTime)+' '+FormatDateTime('HH:mm:ss',DateTimePicker3.Time);

  d2:= FormatDateTime('yyyy-MM-dd',DateTimePicker2.DateTime)+' '+FormatDateTime('HH:mm:ss',DateTimePicker4.Time);

I`ve got it in proper format.
Please explain more complex

Maybe i`ll explain what do I need:
I want to be able to show data in specific period of time so if my customer chooose that he wants to show data from 20-12-2010 to 22-12-2010 and between 08:00:00 and 20:00:00 i suppose to show him data in such range
20-12-2010 and between 08:00:00 and 20:00:00
21-12-2010 and between 08:00:00 and 20:00:00
22-12-2010 and between 08:00:00 and 20:00:00
I want to show a specific shifts of work time for a specific person
I don`t want data which are before 08:00:00 and after 20:00:00 even if the data belongs to date range ( 20-22 12-2010 )
I hope it`s clear now.

Would you please try this:

...
  d1:= FormatDateTime('yyyy-MM-dd HH:mm:ss', StrToDateTime(DateToStr(DateTimePicker1.Date)+TimeToStr(DateTimePicker3.Time))); // change the format to match your configuration
  d2:= FormatDateTime('yyyy-MM-dd HH:mm:ss', StrToDateTime(DateToStr(DateTimePicker2.Date)+TimeToStr(DateTimePicker4.Time)));

  AQSearch.Close;
  AQSearch.SQL.Text := 'SELECT * FROM TABLE WHERE DATE_START BETWEEN :d1 AND :d2';
  AQSearch.Params.ParamByName('d1').AsString := d1;
  AQSearch.Params.ParamByName('d2').AsString := d2;
  AQSearch.Open;
...

Select allOpen in new window

To ensure that you do not have format mismatch make sure to test the query in your DB directly by putting actual datetime:
SELECT * FROM TABLE WHERE DATE_START BETWEEN DateTime1 AND DateTime2

Jimy your query works as all other.
I will use an example. I have 3 records in DATE_START column:
1. 2011-12-29 13:13:13.000
2. 2011-12-29 12:50:08.000
3. 2011-12-29 14:45:27.000
now if i use your query and choose the range with such values:
d1:=  '2011-12-27 06:00:00'; ( this value of time becouse it`s the begining of first shift )
d1:=  '2011-12-30 14:00:00'; ( this value of time becouse it`s the ending of first shift )
By the select I should receive only 2 records:
1 and 2. becouse the time value belongs to period between 06:00:00 and 14:00:00
Now I receive  3 records even if the 3 record time value is bigger then 14:00:00

If i choose range:
d1:=  '2011-12-29 06:00:00';  
d1:=  '2011-12-29 14:00:00
it works ok and show me only 2 records - 1 and 2.

I think you have problem here:

d1:= FormatDateTime('yyyy-MM-dd',DateTimePicker1.DateTime)+' '
    +FormatDateTime('HH:mm:ss',DateTimePicker3.Time);
d2:= FormatDateTime('yyyy-MM-dd',DateTimePicker2.DateTime)+' '
    +FormatDateTime('HH:mm:ss',DateTimePicker4.Time); 

Select allOpen in new window

I think your database dont represent datetime in format you are sending;

try like this:

In uses add DateUtils;

var
  pDay, pMon, pYear, pHour, pMin, pSec, pMSec: Word;
  d1, d2: TDateTime;
begin
  DecodeTime(DateTimePicker3.Time, pHour, pMin, pSec, pMSec); 
  DecodeDate(DateTimePicker1.Date, pDay, pMon, pYear); 

  d1:= EncodeDateTime(pDay, pMon, pYear, pHour, pMin, pSec, pMSec);

  DecodeTime(DateTimePicker4.Time, pHour, pMin, pSec, pMSec); 
  DecodeDate(DateTimePicker2.Date, pDay, pMon, pYear); 

  d2:= EncodeDateTime(pDay, pMon, pYear, pHour, pMin, pSec, pMSec);

  Search.Params.ParamByName('d1').AsDateTime := d1;
  Search.Params.ParamByName('d2').AsDateTime := d2;
end;

Select allOpen in new window

depending on driver you use to access database must mark params as correct type

@ Vaalar,
That sounds logical. I mean getting the result that you got based on the example you shown:
1. 2011-12-29 13:13:13.000
2. 2011-12-29 12:50:08.000
3. 2011-12-29 14:45:27.000

Because if you use the query with the following range:
d1 :=  '2011-12-27 06:00:00';   // begin of shift
d1 :=  '2011-12-30 14:00:00';   // end of shift
You should receive the 3 records, because the range of date & time you selected covers the three records you have there (i.e between 27 and 30 Dec 2011), the time does not matter since the dates are included, 29 Dec is between 27 and 30 Dec.

Now if you want to find out whether your query really works try the following range:

If you choose the following range, you should get the first record only.:
d1:=  '2011-12-29 13:00:00';  
d1:=  '2011-12-29 13:13:15';

And if you choose the range:
d1:=  '2011-12-29 14:00:00';  
d1:=  '2011-12-29 15:00:00';
You should get the third record only.

Making any sense?

Jimy ok but i need a query which shows periods of time
'2011-12-27 from 06:00:00 to 14:00:00
'2011-12-28 from 06:00:00 to 14:00:00
'2011-12-29 from 06:00:00 to 14:00:00
'2011-12-30 from 06:00:00 to 14:00:00
i have written it several times.
I don`t need records from 2011-12-27 06:00:00 to 2011-12-30 14:00:00

I understand, I just used that as an example to show you, here is another example:

These are your records:
1. 2011-12-27 13:13:13.000
2. 2011-12-27 12:50:08.000
3. 2011-12-27 14:45:27.000

Let's consider the following range:
d1 :=  '2011-12-27 06:00:00';  
d1 :=  '2011-12-27 14:00:00';

By using that query, you should get the first and the second records only.

Is that true?

cula99 your solution is working exactly as Jimy`s.

Another example
Source table

id(autoinc)             numbers                   date_start
1                            212                           2011-12-27 13:13:13.000
2                            441                           2011-12-28 11:13:13.000
3                            821                           2011-12-28 16:19:13.000
4                            111                            2011-12-29 15:13:13.000
5                            092                            2011-12-29 11:53:53.000
6                            8822                        2011-12-30 02:13:13.000

Output if i choose period: DateTimePicker1.DateTime='2011-12-27' - date_from
                                         DateTimePicker2.DateTime='2011-12-29' - date_to
                                         DateTimePicker3.Time='12:00:00'
                                         DateTimePicker3.Time='15:00:00'
My result:
1                            212                           2011-12-27 13:13:13.000
5                            092                            2011-12-29 11:53:53.000
 

Jimy you are right but only  if the date part is the same
If we  consider it in range of 1 day your query works perfectly but if we choose a range for example of 3 days it not working as i want, just take a look at my answer to cula99.

Here you go:

...
  d1:= FormatDateTime('yyyy-MM-dd', DateTimePicker1.Date);
  d2:= FormatDateTime('yyyy-MM-dd', DateTimePicker2.Date);
  T1 := FormatDateTime('HH:mm:ss', DateTimePicker3.Time);
  T2 := FormatDateTime('HH:mm:ss', DateTimePicker4.Time);

  AQSearch.Close;
  AQSearch.SQL.Clear;
  AQSearch.SQL.Add('SELECT * FROM TABLE WHERE (ToDate(DATE_START, ''yyyy-MM-dd'') between :d1 and :d2)');
  AQSearch.SQL.Add('and (ToDate(DATE_START, ''HH:mm:ss'') between :T1 and :T2)');
  AQSearch.Params.ParamByName('d1').AsString := d1;
  AQSearch.Params.ParamByName('d2').AsString := d1;
  AQSearch.Params.ParamByName('T1').AsString := T1;
  AQSearch.Params.ParamByName('T2').AsString := T2;
  AQSearch.Open;
...

Select allOpen in new window

Regarding your above example:
id(autoinc)             numbers                   date_start
1                            212                           2011-12-27 13:13:13.000
2                            441                           2011-12-28 11:13:13.000
3                            821                           2011-12-28 16:19:13.000
4                            111                           2011-12-29 15:13:13.000
5                            092                           2011-12-29 11:53:53.000
6                            8822                         2011-12-30 02:13:13.000

If you choose:
                                         DateTimePicker1.DateTime='2011-12-27'
                                         DateTimePicker2.DateTime='2011-12-29'
                                         DateTimePicker3.Time='12:00:00'
                                         DateTimePicker4.Time='15:00:00'

Your result will be:
1                            212                           2011-12-27 13:13:13.000


The record # 5 will not be picked because the time "11:53:53.000" is not in the range "12:00:00 & 15:00:00".

I`ve received: 'ToDate' is not a rocognized built-in function name - after i have run query