Vaalar
asked on
how to filter data by date and time from datetime column
Hi,
I`ve got table with DATE_START column ( datetime ).
I want to allow user to choose the period which he is interested in.
He can choose the date and time from datetimepicker component.
d1:= FormatDateTime('yyyy-MM-dd ',DateTime Picker1.Da teTime)+' '+FormatDateTime('HH:mm:ss ',DateTime Picker3.Ti me);
d2:= FormatDateTime('yyyy-MM-dd ',DateTime Picker2.Da teTime)+' '+FormatDateTime('HH:mm:ss ',DateTime Picker4.Ti me);
here is my query - but it doesn`t work
SELECT * FROM TABLE WHERE DATE_START BETWEEN :d1 AND :d2
Search.Params.ParamByName( 'd1').AsSt ring := d1;
Search.Params.ParamByName( 'd2').AsSt ring := d2;
plz show me a query
I`ve got table with DATE_START column ( datetime ).
I want to allow user to choose the period which he is interested in.
He can choose the date and time from datetimepicker component.
d1:= FormatDateTime('yyyy-MM-dd
d2:= FormatDateTime('yyyy-MM-dd
here is my query - but it doesn`t work
SELECT * FROM TABLE WHERE DATE_START BETWEEN :d1 AND :d2
Search.Params.ParamByName(
Search.Params.ParamByName(
plz show me a query
ASKER
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.
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 ',DateTime Picker1.Da teTime)+' '+FormatDateTime('HH:mm:ss ',DateTime Picker3.Ti me);
d2:= FormatDateTime('yyyy-MM-dd ',DateTime Picker2.Da teTime)+' '+FormatDateTime('HH:mm:ss ',DateTime Picker4.Ti me);
SELECT * FROM TABLE WHERE DATE_START BETWEEN :d1 AND :d2
Search.Params.ParamByName( 'd1').AsSt ring := QuotedStr(d1);
Search.Params.ParamByName( 'd2').AsSt ring := QuotedStr(d2);
d1:= FormatDateTime('yyyy-MM-dd
d2:= FormatDateTime('yyyy-MM-dd
SELECT * FROM TABLE WHERE DATE_START BETWEEN :d1 AND :d2
Search.Params.ParamByName(
Search.Params.ParamByName(
ASKER
Jimy i`ve got:
"Conversion failed when converting date and/or time from character string."
"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.
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.
ASKER
d1:= FormatDateTime('yyyy-MM-dd ',DateTime Picker1.Da teTime)+' '+FormatDateTime('HH:mm:ss ',DateTime Picker3.Ti me);
d2:= FormatDateTime('yyyy-MM-dd ',DateTime Picker2.Da teTime)+' '+FormatDateTime('HH:mm:ss ',DateTime Picker4.Ti me);
I`ve got it in proper format.
Please explain more complex
d2:= FormatDateTime('yyyy-MM-dd
I`ve got it in proper format.
Please explain more complex
ASKER
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.
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:
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
...
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;
...
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
ASKER
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 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:
I think your database dont represent datetime in format you are sending;
try like this:
In uses add DateUtils;
depending on driver you use to access database must mark params as correct type
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 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;
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?
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?
ASKER
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
'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?
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?
ASKER
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:0 0:00'
DateTimePicker3.Time='15:0 0:00'
My result:
1 212 2011-12-27 13:13:13.000
5 092 2011-12-29 11:53:53.000
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='
DateTimePicker2.DateTime='
DateTimePicker3.Time='12:0
DateTimePicker3.Time='15:0
My result:
1 212 2011-12-27 13:13:13.000
5 092 2011-12-29 11:53:53.000
ASKER
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.
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:
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:0 0:00'
DateTimePicker4.Time='15:0 0: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".
...
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;
...
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='
DateTimePicker2.DateTime='
DateTimePicker3.Time='12:0
DateTimePicker4.Time='15:0
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".
ASKER
I`ve received: 'ToDate' is not a rocognized built-in function name - after i have run query
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the values need to be converted to datetime
SELECT * FROM TABLE WHERE DATE_START BETWEEN convert(datetimem,:d1) AND convert(datetime,:d2)