CODEWITHSUNDEEP
mysqldate
Jason Mayo
Jason Mayo

Reputation: 356

MySQL Query not selecting correct date range

Im currently trying to run a SQL query to export data between a certain date, but it runs the query fine, just not the date selection and i can't figure out what's wrong.

SELECT 
    title AS Order_No, 
    FROM_UNIXTIME(entry_date, '%d-%m-%Y') AS Date, 
    status AS Status,
    field_id_59 AS Transaction_ID,  
    field_id_32 AS Customer_Name, 
    field_id_26 AS Sub_Total, 
    field_id_28 AS VAT, 
    field_id_31 AS Discount, 
    field_id_27 AS Shipping_Cost, 
    (field_id_26+field_id_28+field_id_27-field_id_31) AS Total 
FROM 
    exp_channel_data AS d NATURAL JOIN
    exp_channel_titles AS t
WHERE 
    t.channel_id = 5 AND FROM_UNIXTIME(entry_date, '%d-%m-%Y') BETWEEN '01-05-2012' AND '31-05-2012' AND status = 'Shipped'  
ORDER BY 
    entry_date DESC

Upvotes: 2

Views: 130

Answers (2)

eggyal
eggyal

Reputation: 126005

As explained in the manual, date literals should be in YYYY-MM-DD format. Also, bearing in mind the point made by @ypercube in his answer, you want:

WHERE t.channel_id = 5
  AND entry_date >= UNIX_TIMESTAMP('2012-05-01')
  AND entry_date <  UNIX_TIMESTAMP('2012-06-01')
  AND status = 'Shipped'

Upvotes: 6

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

Besides the date format there is another issue. To effectively use any index on entry_date, you should not apply functions to that column when you use it conditions in WHERE, GROUP BY or HAVING clauses (you can use the formatting in SELECT list, if you need a different than the default format to be shown). An effective way to write that part of the query would be:

 (  entry_date >= '2012-05-01'  
AND entry_date < '2012-06-01' 
 )

It works with DATE, DATETIME and TIMESTAMP columns.

Upvotes: 3

Related Questions