I have a mysql query which works in a strange way. I am posting the 2 queries with input data changed and the output are listed under each query.

Query 1 (Area to be noted `BETWEEN '13/05/11' AND '30/05/11'`):

```

SELECT COUNT(pos_transaction_id) AS total,
DATE_FORMAT(pt.timestamp,'%d-%m-%Y %H:%i:%S') AS Date,
SUM(amount) AS amount
FROM pos_transactions pt 
WHERE DATE_FORMAT(pt.timestamp,'%e/%m/%y') BETWEEN '13/05/11' AND '30/05/11'
GROUP BY WEEK(pt.timestamp) ORDER BY pt.timestamp

```

Output:  

![enter image description here](https://i.stack.imgur.com/PESz0.jpg) 

Query 2 (Area to be noted `BETWEEN '3/05/11' AND '30/05/11'`):

```

SELECT COUNT(pos_transaction_id) AS total,
DATE_FORMAT(pt.timestamp,'%d-%m-%Y %H:%i:%S') AS Date,
SUM(amount) AS amount
FROM pos_transactions pt 
WHERE DATE_FORMAT(pt.timestamp,'%e/%m/%y') BETWEEN '3/05/11' AND '30/05/11'
GROUP BY WEEK(pt.timestamp) ORDER BY pt.timestamp
```

Output:  

![enter image description here](https://i.stack.imgur.com/Z6Ne6.jpg) 
Now when the range is increased in the second query why am I getting just one record ? And even in the first query I am getting records which is out of range. What is wrong with it?? 

EDIT

The changed query looks like this and still not doing what I wanted it to do. 

```

SELECT COUNT(pos_transaction_id) AS total,
DATE_FORMAT(pt.timestamp,'%d-%m-%Y %H:%i:%S') AS Date,
SUM(amount) AS amount
FROM pos_transactions pt
WHERE DATE_FORMAT(pt.timestamp,'%e/%m/%y') BETWEEN STR_TO_DATE('01/05/11','%e/%m/%y') AND STR_TO_DATE('30/05/11','%e/%m/%y')
GROUP BY WEEK(pt.timestamp) ORDER BY pt.timestamp
```

The output is:
![enter image description here](https://i.stack.imgur.com/i0vae.jpg)