MYSQL extract day number from day

Question

Is it possible to extertract and integer value from "Mon", Tue", "Wed" etc with an SQL statment?

For example Mon = 1 Tue = 2 Wed = 3 etcv

Answer 1

If you simply have a non-datetime field with just Mon, Tue, Wed, etc, you can use the STR_TO_DATE() and WEEKDAY() functions to come up with something like this:

SELECT WEEKDAY(STR_TO_DATE(CONCAT('201011 ', yourField), '%X%V %W')) + 1 AS WeekIndex;

Test Case:

SELECT WEEKDAY(STR_TO_DATE(CONCAT('201011 ', 'Mon'), '%X%V %W')) + 1 AS WeekIndex;
+-----------+
| WeekIndex |
+-----------+
|         1 |
+-----------+

SELECT WEEKDAY(STR_TO_DATE(CONCAT('201011 ', 'Tue'), '%X%V %W')) + 1 AS WeekIndex;
+-----------+
| WeekIndex |
+-----------+
|         2 |
+-----------+

SELECT WEEKDAY(STR_TO_DATE(CONCAT('201011 ', 'Wed'), '%X%V %W')) + 1 AS WeekIndex;
+-----------+
| WeekIndex |
+-----------+
|         3 |
+-----------+

SELECT WEEKDAY(STR_TO_DATE(CONCAT('201011 ', 'Thu'), '%X%V %W')) + 1 AS WeekIndex;
+-----------+
| WeekIndex |
+-----------+
|         4 |
+-----------+

Answer 2

Try FIELD:

SELECT FIELD('Mon', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
    -> 1
SELECT FIELD('Thu', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
    -> 4

http://dev.mysql.com/doc/refman/5.4/en/string-functions.html#function_field

Answer 3

If your field is a date field, just use

SELECT DATE_FORMAT(my.field, "%w");

More Info on MySQLs DATE_FORMAT can be optained here.