MySQL Date range query
I have a MySQL query to gather information for a few date ranges. The following query works well for the current month, but things need to change a little, as it is not exactly what I require.
Here is the scenario...
SELECT * FROM TABLE
WHERE YEAR(date_added) = YEAR(CURDATE())
AND MONTH(date_added) = MONTH(CURDATE())
Staff get paid on the last FRIDAY of every month.
The commission cut off date is the FRIDAY before the last Friday.
The week Staff get paid gets added onto the following months information.
I need to somehow be able to select this information via the date_added field.... complex i know! :confused:
So i think it needs to be something like...
FIND values BETWEEN the
PREVIOUS months last week
SECOND last Friday of the CURRENT MONTH.
Can anyone whom knows MYSQL help me figure this one out?
I'd probably tackle the date determination in the application code (such as with a combination of PHP strtotime() and date() functions) to come up with the values to stick into the SQL in a BETWEEN statement. Is that feasible in your case, or do you have to do it in the SQL?
This is an example which you van use to select the information between any date range.
between '2014-01-01' and '2014-01-31'