12

I am trying to understand the both but I am getting really confused. Online it says:

MySQL WEEKDAY() returns the index of the day in a week for a given date (0 for Monday, 1 for Tuesday and ......6 for Sunday). MySQL DAYOFWEEK() returns the week day number (1 for Sunday,2 for Monday …… 7 for Saturday ) for a date specified as argument.

Can anyone explain with an example for both? Thank you.

Acerace.py
  • 679
  • 3
  • 9
  • 25
  • 1
    `select weekday(now()), dayofweek(now())` - there’s your “example”. And if you still have trouble understanding anything in particular about this - then start by describing what that is. Please go read [ask]. – CBroe Dec 01 '17 at 09:01
  • 3
    `DAYOFWEEK` uses the ODBC standard while `WEEKDAY` does not. Other than this, the documentation reveals no difference that I can see. – Tim Biegeleisen Dec 01 '17 at 09:06

2 Answers2

8

A relevant difference is that weekday counts the days of the week from Monday, as follows and start from 0

0=Monday, 1=Tuesday, 2=Wednesday, 3=Thursday, 4=Friday, 5=Saturday, 6=Sunday

On the other hand dayofweek counts the days of the week from Sunday and start with 1

1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday

Please try

select weekday(now()) weekday, dayofweek(now()) dayofweek;

Mario
  • 4,784
  • 3
  • 34
  • 50
3

WEEKDAY() returns an index from 0 to 6 for Monday to Sunday
DAYOFWEEK() returns an index from 1 to 7 for Sunday to Saturday
So, the same day has different indexes, eg. Sunday can be 1 or 6

Often I prefer to use DAYOFWEEK() because SQL functions like ELT() are 1-index based :

SELECT ELT(DAYOFWEEK(NOW()), 'Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat');
SELECT ELT(WEEKDAY(NOW()) + 1, 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
JCH77
  • 1,125
  • 13
  • 13