search query problem

[raj_2006]

Hi

I have a date type field st.

I have a record in the db where st=2006-10-06

So cant i fetch this record if i use this query:

$sql="SELECT * FROM transaction WHERE st BETWEEN STR_TO_DATE('$start', '%Y/%m/%d') AND STR_TO_DATE('$end', '%Y/%m/%d')";

I am sending
$start=2006-10-06
$end=2006-10-06

But unfortunately no rows is fetched.

Plz suggest me if i am doing any wrong with the sql.

Thanks
Raj

[chazzy]

what's the type on st?

[raj_2006]

The data type of st is date

0000-00-00

//i am inserting as $st=date("Y-m-d");

[chazzy]

are you positive it is a date? typically, you can't insert in that format if it is a date. can you run a describe on the table and show the output? the issue you're describing is when the column is not a date.

another thing you can try is


SELECT * FROM transaction WHERE ((st BETWEEN STR_TO_DATE('$start', '%Y/%m/%d') AND STR_TO_DATE('$end', '%Y/%m/%d')) OR (st = STR_TO_DATE('$start', '%Y/%m/%d')) OR (st = STR_TO_DATE('$end', '%Y/%m/%d')) ;

[raj_2006]

Field Type Null Key Default Extra
tid int(9) PRI NULL auto_increment
st date 0000-00-00

This is the table desc.

and also while running this query i am getting sql error

SELECT * FROM transaction WHERE ((st BETWEEN STR_TO_DATE('2006-10-6', '%Y/%m/%d') AND STR_TO_DATE('2006-10-6', '%Y/%m/%d')) OR (st = STR_TO_DATE('2006-10-6', '%Y/%m/%d')) OR (st = STR_TO_DATE('2006-10-6', '%Y/%m/%d')) ;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1.

[chazzy]

depending on how you execute this, you probably don't want the ' ;' included in your query.

[raj_2006]

sorry i am using in this way:


$sql="SELECT * FROM transaction WHERE ((st BETWEEN STR_TO_DATE('$start', '%Y/%m/%d') AND STR_TO_DATE('$end', '%Y/%m/%d')) OR (st = STR_TO_DATE('$start', '%Y/%m/%d')) OR (st = STR_TO_DATE('$end', '%Y/%m/%d'))" ;

[chazzy]

based on your input, shouldn't it be %Y-%m-%d?

[raj_2006]

I tried with given - in place of /

but still its giving sql error.....

$sql="SELECT * FROM transaction WHERE ((st BETWEEN STR_TO_DATE('$start', '%Y-%m-%d') AND STR_TO_DATE('$end', '%Y-%m-%d')) OR (st = STR_TO_DATE('$start', '%Y-%m-%d')) OR (st = STR_TO_DATE('$end', '%Y-%m-%d'))" ;