I have to write a query that selects Last month orders from a table Order( id , ......., order_date).
Assume that the date of today is 4th of April. Will i select orders from 1st March to 31th of March or from the 4th of March to the 4th of April ???
The first query :
SELECT * from Order where order_date between sysdate() and sysdate - INTERVAL 1 Month
SELECT * from Order where order_date between (Last_day(sysdate()) - INETRVAL 1 MONTH)
and ((Last_day(sysdate()) + INTERVAL 1 DAY) - INETRVAL 2 MONTH))
(Last_day(sysdate()) - INETRVAL 1 MONTH)
((Last_day(sysdate()) + INTERVAL 1 DAY) - INETRVAL 2 MONTH))
"Last month" typically means the whole period of the month prior to today. So if today was 4th April then last month is
>= 1st March and < 1st April
Select * from orders Where order_date >= '2016-03-01' and order_date < '2016-04-01'
Note I recommend avoiding between for date ranges. Also note that the time of 23:59:59 is NOT the end of a day and that recent versions of mysql do support subsecond time precision.