How to get friday last week in MYSQL

Question

How to get friday last week ?

I have tried code the folllowing, but it not forward not backward.

SELECT DATE_FORMAT(LAST_DAY(NOW()) - ((7 + WEEKDAY(LAST_DAY(NOW())) - 4) % 7), '%Y-%m-%d') last_friday;

The result that i need should be: 2013-05-24 not 2013-05-31.

Please change my coding.

Answer 1

Dude, try this one:

SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL ((7 + WEEKDAY(DATE_SUB(NOW(), INTERVAL 1 WEEK)) - 4) % 7) DAY), '%Y-%m-%d');

Answer 2

In MySQL, the WEEKDAY function returns an integer from 0 to 6: 0 for Monday, 1 for Tuesday etc. If you take that value as the number of days and subtract it from the current date, i.e. like this:

NOW() - INTERVAL WEEKDAY(NOW()) DAY

you will always get the current [ISO] week's Monday.

Knowing that, you will only need to subtract 3 more days to get the last week's Friday. So, the final expression would go like this:

NOW() - INTERVAL WEEKDAY(NOW()) + 3 DAY

You can take a look at this SQL Fiddle demo as well.