MySQL DAYOFWEEK() - my week begins with monday

Mysql

Mysql Problem Overview


I'm using DAYOFWEEK() function in MySQL which returns 1 for sunday. But in my country the week starts with monday, not sunday. Is there any chance to get dayofweek from MySQL formated like: (1 - Monday, 2 - Tuesday, ...) ?

Mysql Solutions


Solution 1 - Mysql

Use WEEKDAY() instead of DAYOFWEEK(), it begins on Monday.

If you need to start at index 1, use or WEEKDAY() + 1.

Solution 2 - Mysql

Try to use the WEEKDAY() function.

> Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = > Sunday).

Solution 3 - Mysql

How about subtracting one and changing Sunday

IF(DAYOFWEEK() = 1, 7, DAYOFWEEK() - 1)

Of course you would have to do this for every query.

Solution 4 - Mysql

You can easily use the MODE argument:

MySQL :: MySQL 5.5 Reference Manual :: 12.7 Date and Time Functions

If the mode argument is omitted, the value of the default_week_format system variable is used:

MySQL :: MySQL 5.1 Reference Manual :: 5.1.4 Server System Variables

Solution 5 - Mysql

Could write a udf and take a value to tell it which day of the week should be 1 would look like this (drawing on answer from John to use MOD instead of CASE):

DROP FUNCTION IF EXISTS `reporting`.`udfDayOfWeek`;
DELIMITER |
CREATE FUNCTION `reporting`.`udfDayOfWeek` (
  _date DATETIME,
  _firstDay TINYINT
) RETURNS tinyint(4)
FUNCTION_BLOCK: BEGIN
  DECLARE _dayOfWeek, _offset TINYINT;
  SET _offset = 8 - _firstDay;
  SET _dayOfWeek = (DAYOFWEEK(_date) + _offset) MOD 7;
  IF _dayOfWeek = 0 THEN
    SET _dayOfWeek = 7;
  END IF;
  RETURN _dayOfWeek;
END FUNCTION_BLOCK

To call this function to give you the current day of week value when your week starts on a Tuesday for instance, you'd call:

SELECT udfDayOfWeek(NOW(), 3);

Nice thing about having it as a udf is you could also call it on a result set field like this:

SELECT
  udfDayOfWeek(p.SignupDate, 3) AS SignupDayOfWeek,
  p.FirstName,
  p.LastName
FROM Profile p;

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionpiccaView Question on Stackoverflow
Solution 1 - MysqlnosView Answer on Stackoverflow
Solution 2 - MysqlgerasimView Answer on Stackoverflow
Solution 3 - MysqlJustin GiboneyView Answer on Stackoverflow
Solution 4 - MysqlwebDEVILopersView Answer on Stackoverflow
Solution 5 - MysqlcodemonkeyView Answer on Stackoverflow