selecting a date range

meshi · 11-22-2007, 09:36 AM

hi all i have a problem querying date

$from='2007-11-14';
$to='2007-11-16';
$query="select * from table where field_date between '$from' and '$to'";

it displays range from 2007-11-14 to 2007-11-15.?why is 2007-11-16 is not included?? how would i query then if i want to include 2006-11-16 without changing the value of my variables.

Wildhoney · 11-22-2007, 01:13 PM

2007-11-16 should be included in your BETWEEN clause. Perhaps that is not where the problem lies?

Try the following, which should behave exactly the same as BETWEEN:

sql Code:

SELECT
    *
FROM
    myTable
WHERE
    myDateColumn >= '1982-10-10'
AND
    myDateColumn <= '1985-10-10'

Salathe · 11-22-2007, 01:25 PM

What is the column type for field_date? It's only a hypothesis but perhaps the dates are being calculated as BETWEEN 2007-11-14 00:00:00 AND 2007-11-16 00:00:00 which would explain why any rows belonging to the 16th wouldn't be caught.

meshi · 11-29-2007, 01:30 PM

yeah.. ur right salathe..the type is timestamp so it would not display the greater than 2007-11-16 00:00:00. its working now...thanks guys