date difference

the query failed for some dates

[bluemagix]

hello all,

i am doing the following query

SELECT * FROM request, response, advt
 WHERE request.request_id = response.request_id
 AND advt.ad_uid = response.ad_uid
 AND request.date BETWEEN '$from_date' AND '$to_date'

and is giving me the result is i select the dates in one month
but if i select one date from one month
and other date from different month then the query fails..
[e.g. - date in sept(ie 9th month ) and oct(10 month) date are compared then result is not displaying..

but if aug date and sept date are compare then we can have the result..

why it is so..?

[omoutop]

welcome to webmasterworld

First of all you dont mention what type your date field is.
Second, are there any record on october? Or in any month the query fails?

[penders]

AND request.date BETWEEN '$from_date' AND '$to_date'

By enclosing your dates in single quotes you are implying you are using date-strings? But (I'm guessing) if your request.date is also a string then it's going to perform a string comparison, not a date comparison? If request.date is a DATE type then you might need to CAST() your date-strings to DATE ?

[venelin13]

Check the values you have for the dates. They should always be in the format of YYYY-MM-DD

- 4 digits for the year;
- 2 digits for the month;
- 2 digits for the day;

An example of incorrect date for 1st Oct 2008 is 2008-10-1

and an example for incorrect month value: 2008-9-15

[grallis]

If I were you, I'd use a more basic approach like 'WHERE this.date < max.date AND this.date > min.date'

BETWEEN will probably require the use of the MySQL CAST() function, to properly compare the two dates. It's been a while since I've used BETWEEN, but I imagine it would be something like this:

SELECT * FROM request, response, advt
WHERE request.request_id = response.request_id
AND advt.ad_uid = response.ad_uid
AND request.date BETWEEN CAST('$from_date', date) AND CAST('$to_date', date) 

Also note, if you use CAST(), and you have some dates as datetimes and others as just dates, you'll need to use CAST() to cast the offending date to the right type i.e. - datetime or date