Please Help with Query

can't get the between statement to work

[chasehx]

Here is my query i am using:

$sql = "SELECT * FROM leads WHERE referrer_id = 24 AND date_added BETWEEN $date1 AND $date2 AND status_id = 5 ORDER BY id DESC";

My date format is as such 2010-02-01 and the time format of the database is a timestamp, such as 2010-02-01 05:12:25PM. I am not sure if it is this small difference that is making my query fail. The thing is, it does not give me a empty result set error, it just doesn't do anything. I know there are rows that match, but the page stays blank. Any suggestions?

[LifeinAsia]

What are the values of @date1 and $date2? Are they actual date values or VARCHAR values? If VARCHAR, convert to DATETIME before doing the comparison.

[chasehx]

They are inputs from a JS calender on the previous page. the calender is configured to give the dates in the format mentioned, it just doesn't work..

[LifeinAsia]

You'll probably need to convert them to DATETIME values.

[chasehx]

I tried using the convert(datetime, $date1) and it makes the query invalid. without it, the result is empty, if I have that, it throws an error. Is it because the date range I am using to compare doesn't have the H:i:s? I tried using date(Y-m-d H:i:s, $date1) but it doesn't work, sets the value of the variable to 1969, meaning it was invalid.

[LifeinAsia]

Try convert($date1,date)

[chasehx]

what the hell removing the referrer_id clause makes the date range work. what is the problem with the where clause? it never gives me issues.

[chasehx]

Nevermind, it still doesn't work. I finally got the date function to convert it into the SQL friendly format without returning 1969, but still no rows show up. Even with the referrer clause removed I still can't get it. I tried convert, tried using UNIX_TIMESTAMP as the comparison, still nothing.

[LifeinAsia]

Just to confirm- the date_added field is a DATETIME or TIMESTAMP field?

[rocknbil]

If you're talking about mySQL (presuming you are) the timestamp format is identical to datetime, it does not have an AM/PM and is a 24 hour clock.

If the incoming date is date only, try the below. If it includes a time with the AM/PM tacked on, you'll have to prepare the input string before querying, remove the textual part, and increment the hour if it's > 12.

$sql = "SELECT * FROM leads WHERE referrer_id = 24 AND date_added (BETWEEN '$date1' AND '$date2') AND status_id = 5 ORDER BY id DESC";

or this,

$sql = "SELECT * FROM leads WHERE referrer_id = 24 AND date_added (BETWEEN '$date1 00:00:00' AND '$date2 59:59:59') AND status_id = 5 ORDER BY id DESC";

or this

$sql = "SELECT * FROM leads WHERE referrer_id = 24 AND date_added >= '$date1 00:00:00' AND date_added <= '$date2 59:59:59' AND status_id = 5 ORDER BY id DESC";

remembering the single quotes won't affect the interpolation, they are "just another character," the PHP string delim's are ", but the quoting is needed by mySQL.

mySQL Date, Datetime, Timestamp [dev.mysql.com]