Need help on mysql date range query

Question

Below query I am using in my search

I have date range in search, when I searching data between '01/05/2012' AND '31/05/2012' date range but query return all record set.

SELECT t.created date, t.saleid sale_id, u.id user_id, NULL merchant_name, cheque_number cheque_number, u.first_name name, (
SELECT company_name
FROM users
WHERE id = u.my_charity_id)charity_name, t.campaignname website, t.campaignid campaign_id, t.mysoko_discount discount_percentage, t.discount, t.salecommission sale_commission, t.salevalue total_sale_value, t.salestatus sale_status
FROM `transaction` AS `t` , `users` AS `u`
WHERE DATE_FORMAT( t.created, '%d/%m/%Y' )
BETWEEN '01/05/2012' AND '31/05/2012'
AND t.user_id = u.id
LIMIT 0 , 30

I am looking for date range search data but result return all data from table.

date            sale_id       user_id   
2012-04-19 00:00:00     20253305    45  
2012-04-11 00:00:00     20253306    68  
2012-04-23 00:00:00     20253307    68  
2012-04-25 00:00:00     20253308    45  
2012-04-27 00:00:00     20253309    45  
2012-04-29 00:00:00     20253310    68  
2012-04-30 00:00:00     20253311    45  
2012-05-01 00:00:00     20253312    45  
2012-05-03 00:00:00     20253313    68  
2012-04-18 00:00:00     20253314    4

My search is from date range txn search (from / to date)

INPUT date format: DD/MM/YYYY

score 1 · Answer 1 · answered May 27 '12 at 00:08

You could try filtering rows like this:

WHERE t.created >= '2012-05-01'
  AND t.created <  '2012-06-01'

If the month is specified as an argument, you could use the following calculations in the WHERE clause:

WHERE t.created >= @monthdate
  AND t.created <  @monthdate + INTERVAL 1 MONTH

score 1 · Accepted Answer · answered May 27 '12 at 03:09

Do this:

WHERE t.created BETWEEN '2012-05-01 00:00:00' AND '2012-05-31 23:59:59' ...

MySQL will interpret '2012-05-31 23:59:59' as the appropriate DATETIME or TIMESTAMP type, allowing you to take advantage of an index on t.created.

Your problem is that your current query specifies that the string representation of t.created be between the strings '01/05/2012' and '31/05/2012'. Since you string format day-month-year, any date whose day component is between '02' and '30', inclusive, will match. (And the first of any month of May or later will match, etc.)

score 0 · Answer 3 · answered May 26 '12 at 18:20

0

One option:

 WHERE MONTH(t.created) = 5 AND YEAR(t.created) = 2012

answered May 26 '12 at 18:20

John Conde

217,595
99
455
496

score 0 · Answer 4 · answered May 27 '12 at 02:31

0

SELECT t.created date, t.saleid sale_id, u.id user_id, NULL merchant_name, cheque_number cheque_number, u.first_name name, (
SELECT company_name
FROM users
WHERE id = u.my_charity_id)charity_name, t.campaignname website, t.campaignid campaign_id, t.mysoko_discount discount_percentage, t.discount, t.salecommission sale_commission, t.salevalue total_sale_value, t.salestatus sale_status
FROM `transaction` AS `t` , `users` AS `u`
WHERE DATE_FORMAT( t.created, '%d/%m/%Y' )
BETWEEN STR_TO_DATE('01/05/2012','%d,%m,%Y') AND STR_TO_DATE('31/05/2012','%d,%m,%Y')
AND t.user_id = u.id
LIMIT 0 , 30

answered May 27 '12 at 02:31

Sebas

21,192
9
55
109

you will have to be sure that t.created is of date format as well – Sebas May 27 '12 at 02:33
Sebas I am getting empty result MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0418 sec ) – Elankeeran May 27 '12 at 02:40

Need help on mysql date range query

4 Answers4

Linked