Need help with SQL

Startbeitrag von Yogi Yang am 06.08.2013 15:11

Hello,

I have been banging my head with this problem for 3 days now but am not able to solve it at my end so I am posting it here for help.

Here is the data structure of a table/file from which I am trying to retrieve data in correct manner.

[BookingForm]
Fields:
[bf_id] integer
[bf_num]
[bf_members]
[bf_from_date]
[bf_to_date]
[bf_name]
[bf_cancelled]


I this data file there is quite a lot of data.

I want to find whether a date selected by user falls withing any data range already entered.

I am using following SQL for this:

SELECT * From [BookingForm] WHERE (('20130604') BETWEEN [bf_from_date] AND [bf_to_date]) OR ((''20130605'') BETWEEN [bf_from_date] AND [bf_to_date])) AND [bf_cancelled] = 0

This SQL seems to return data if the date entered by user falls between previously entered start and end dates.

Say for example there are following dates:
Start Date End Date
---------- ----------
04-May-2013 10-May-2013
15-May-2013 16-May-2013
29-May-2013 30-May-2013
02-Jun-2013 02-Jun-2013
05-Jun-2013 07-Jun-2013

For example if user enters Start Date and End Date as: 04-Jun-2013 05-Jun-2013
The query will fetch the record with "05-Jun-2013 07-Jun-2013" dates.

But if user enters say for example:
Start Date and End Date as: 11-May-2013 15-Jun-2013
The query does not return any records, while actually it should fetch the record with dates: 15-May-2013 16-May-2013

Can some one please help in solving this problem?

TIA

Yogi Yang

Antworten:

Hi Yogi,

You have two single quotes around one of your dates, while this might not be the problem, it probably won't help.

You should also check the number of brackets you have in your query. i.e. you may need to clearer to the DB that you want 1 OR 2 AND 3, or you want (1 OR 2) AND 3. This can have a big impact on the results you see.

Thanks,
Ned!

von Ned! - am 06.08.2013 15:49
Hi,

I also just noticed that you aren't really using any criteria in your query that matches on the fields in the table.

What you really need is something more like...

WHERE (BookingForm.Date BETWEEN [bf_from_date] AND [bf_to_date])

Thanks,
Ned!

von Ned! - am 06.08.2013 15:57
Hi
Is it not simply like this :

SELECT * From [BookingForm] WHERE (StartDate >= [bf_from_date]) AND (EndDate

von Viggo Poulsen - am 07.08.2013 06:33
Quote
Ned!
You have two single quotes around one of your dates, while this might not be the problem, it probably won't help.

Sorry for two single quotes. It is my typing mistake.

von Yogi Yang - am 07.08.2013 15:47
Quote
Ned!
WHERE (BookingForm.Date BETWEEN [bf_from_date] AND [bf_to_date])
Here 'BookingForm.Date' is a date entered by user. It does not come from any DB field.

von Yogi Yang - am 07.08.2013 15:49
Hi Yogi,

Something like this should do it then...


Select * from BookingForm WHERE (((BookingForm.Bf_FromDate BETWEEN '20130511' AND '20130615') OR (BookingForm.Bf_ToDate BETWEEN '20130511' AND '20130615')) AND BookingForm.bf_cancelled = 0)


Replace '20130511' and '20130615'... with the dates provided by the user.

Thanks
Ned!

von Ned! - am 07.08.2013 16:20
Quote
Ned!
Hi Yogi,

Something like this should do it then...


Select * from BookingForm WHERE (((BookingForm.Bf_FromDate BETWEEN '20130511' AND '20130615') OR (BookingForm.Bf_ToDate BETWEEN '20130511' AND '20130615')) AND BookingForm.bf_cancelled = 0)


Replace '20130511' and '20130615'... with the dates provided by the user.

Thanks
Ned!


Thanks man!

This seems to have solved my problem.

Regards,

Yogi Yang

von Yogi Yang - am 08.08.2013 13:39
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.