Filter Date fields using SQL Query 1

[EricDraven]

I am having a little problem using a query to return a set of results from a paradox table. Hopefully somebody can point out what I am doing wrong.

BookQuery.SQL.Clear;
BookQuery.SQL.Add('Select * from Bookings');
BookQuery.SQL.Add('where BOOKDATE >= ' + QuotedStr(RangeStart));
BookQuery.SQL.Add('and BOOKDATE <= ' + QuotedStr(RangeEnd));
BookQuery.Open;

The two variables are both of String type and are just user entered dates. The problem is that this query is only returning one record whereas, if I filter a TTable component using the same parameters, I am returned several hundred records (the correct amount). This is the only piece of SQL to be added to my progam before completion and its now driving me nuts.

Knowing me its probably another case of "I cant see the wood for those pesky trees"


When your feeling down and your resistance is low, light another cigarette and let yourself go

 

[LucieLastic]

hi

Firstly, I'd use 'Between' for the range(assuming it accepts it).

BookQuery.SQL.Add('where BOOKDATE Between ' + QuotedStr(RangeStart)+' and '+ QuotedStr(RangeEnd));

Your problem is most likely your date format, check it's the right format for the database.

lou

 

[EricDraven]

Weez

I have discovered since posting that it is indeed the format of the date. My program is taking the date as American format instead of English but my system settings are all correct. I am using exactly the same setup as when I used a TTable component and filtering which returned the data correctly.

Am I missing something about the queries or could it be my Table structure (paradox table, date field type)?

Thanks


When your feeling down and your resistance is low, light another cigarette and let yourself go

 

[EricDraven]

Follow up time!

I have fixed the problem now. You need to use parameters to change the date format from American. The following code fixed the problem.

BookQuery.SQL.Add('select * from Bookings');
BookQuery.SQL.Add('where BOOKDATE Between :RangeStart and :RangeEnd');
BookQuery.ParamByName('RangeStart').AsDate := StrToDate(MaskEdit9.Text);
BookQuery.ParamByName('RangeEnd').AsDate := StrToDate(MaskEdit10.Text);
BookQuery.Open;

When your feeling down and your resistance is low, light another cigarette and let yourself go

 

[lespaul]

Just an FYI - I have found that using the parameters for queries takes much longer than just passing in the variables. I'm still trying to figure out how to get the queries to work faster (of course there are 20 - 30 of them that have to run to gather the information since the database is so unnormalized!). But when I stopped using the parameters it cut 20 - 30 seconds off the processing time.

BookQuery.SQL.Add('select * from Bookings where BOOKDATE Between ' + StrToDate(MaskEdit9.Text) + ' and ' + StrToDate(MaskEdit10.Text));
BookQuery.Open;




Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[LucieLastic]

hi

Just one more thing I discovered: I found using Params or PAramByname much slower than specifying the whole string, don't know why. I'd suggest if you can, supply the whole string like this and compare the time taken with the Open:


BookQuery.SQL.Add('select * from Bookings');
BookQuery.SQL.Add('where BOOKDATE Between '+quotedstr(StrToDate(MaskEdit9.Text))+' and '+Quotedstr(StrToDate(MaskEdit10.Text));
BookQuery.Open;


lou

 

[EricDraven]

When I originally coded the query, I wrote it virtually identical to the way in which you two have stated here. Unfortunately, performing the query this way,does so in American Date format despite the system settings. The only way around this is to use the parameters of the query. This way it returns the results based on the system date format.

The only way I can get your query to work is to change the field type from date to something else (Integer perhaps) but as the system is in circulation it is too late for that.

Thanks for the suggestions though as they are greatly received indeed and I appear to need as many pointers as possible regarding SQL.

Cheers


When your feeling down and your resistance is low, light another cigarette and let yourself go

 

[LucieLastic]

hi

Keep it simple where you can. Could you not use the Calender component for your dates rather than MaskEdit? With that you can use the long date format which will abolish any future us/uk date format problems/misinterpretations at the users' end?

Just a thought, I'll go now.... )

lou

 

[LucieLastic]

...oh, and with that you could then have

BookQuery.SQL.Add('select * from Bookings');
BookQuery.SQL.Add('where BOOKDATE Between '+quotedstr(FormatDateTime('mm/dd/yyyy',dtpfromdate.date))+' and '+Quotedstr(FormatDateTime('mm/dd/yyyy',dtpTodate.date);

lou

...definitely going now...

 

[EricDraven]

Weez
Just a quicky. Shouldnt this be...

BookQuery.SQL.Add('where BOOKDATE Between '+quotedstr(FormatDateTime('dd/mm/yyyy',dtpfromdate.date))+' and '+Quotedstr(FormatDateTime('dd/mm/yyyy',dtpTodate.date);

...in order to return the date in European format?


When your feeling down and your resistance is low, light another cigarette and let yourself go

 

[LucieLastic]

hi

I did it that way because I thought your database was expecting US format, or is it the other way round, interface is US and db is UK?

Anyway, you get the gist of it. Just thought the FormatDateTime gave more versatility for your query. (there was an error in my other post: shouldn't of had: quotedstr(StrToDate(MaskEdit9.Text)) as needed it as a string, btw).

In theory, you can use Convert (in select statement) to put the date in the format you want. If you want more info on this, feel free to ask. Does Paradox recognise Convert, btw?

lou

 

[EricDraven]

Well whaddaya know. It does work with FormatDateTime function. I had the days and months the wrong way around. I just need to test it on some other machines to make absolutley certain that its not just my backwards computer and away we go!

Many, many thanks!


When your feeling down and your resistance is low, light another cigarette and let yourself go