CODEWITHSUNDEEP
mysql
Jam Dara
Jam Dara

Reputation: 249

How to get friday last week in MYSQL

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.

Upvotes: 8

Views: 12686

Answers (5)

James Shelby
James Shelby

Reputation: 386

If on Friday you want last Friday, on Saturday you want yesterday

select now() - interval (weekday( now())+ 2)% 7+ 1 day 'Previous Friday';

I was likewise looking for a solution for accounting, where I need a full Friday of data. So if today is Friday, then I need now()- interval 7 day. However, I may get excited and want to run the query on Saturday, and would expect now()- interval 1 day. In view of this, the interval should always be between 1 and 7 in my case. I looked at all previous answers, however for me they did not quite meet my needs. Here is a chart showing each person's "subtract interval" for each possible weekday (0= Monday):

+---------+---------+------+----------+------+-----------+
| weekday |    mine | KaeL | Andriy M | dArc | dquinonez |
+---------+---------+------+----------+------+-----------+
|       0 |       3 |    3 |        3 |    3 |         3 |
|       1 |       4 |    4 |        4 |    4 |         1 |
|       2 |       5 |    5 |        5 |    5 |         1 |
|       3 |       6 |    6 |        6 |    6 |         1 |
|       4 |       7 |    0 |        7 |    7 |         1 |
|       5 |       1 |    1 |        8 |    8 |         1 |
|       6 |       2 |    2 |        9 |    9 |         2 |
+---------+---------+------+----------+------+-----------+

mysql> select i 'weekday',
  (i+ 2)% 7+ 1 'mine',
  ((7+ i- 4)% 7) 'KaeL',
  i+ 3 'Andriy M',
  ((7+ i- 4)% 14) 'dArc',
  case i when 0 then 3 when 6 then 2 else 1 end 'dquinonez'
from (select 0 i union select 1 union select 2 union select 3
  union select 4 union select 5 union select 6 ) q;
  • 'Kael' stays on today when today is Friday (you may want that.)
  • 'Andriy M' and 'dArc' go back 2 Fridays on Saturday and Sunday (which is good if you don't want to look at last Friday until the weekend has past.) Also 'dArc' gives the last Friday of the month using 'last_day', which the original post was using, so this may have been what the original question actually was.
  • 'dquinonez' works for me on Sat, Sun, and Mon - which is good if I know I will be running the query on those days only. His method is also the most readable when filled out with the other days - and easiest to modify for special cases.

Upvotes: 1

dquinonez
dquinonez

Reputation: 123

Try This

SELECT date_sub(curdate(), INTERVAL  
CASE weekday(curdate()) -- weekday will give you the index of the day ( 0 = Monday, 1 = Tuesday, 2 = Wednesday, ... 6 = Sunday)  
WHEN 0 THEN 3 -- If Monday then sub 3 days  
WHEN 6 THEN 2 -- If Sunday then sub 2 days  
ELSE 1 -- Else sub 1 day  
END DAY);

Upvotes: 1

Andriy M
Andriy M

Reputation: 77657

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.

Upvotes: 10

dArc
dArc

Reputation: 342

try this

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

Upvotes: 0

KaeL
KaeL

Reputation: 3659

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');

Upvotes: 11

Related Questions