Have Geek Brain

Extract day number from day string

Is it possible to extract integer value from day names ie "Mon", Tue "," Wed "with SQL expression?

For instance:

 Mon = 1
 Tue = 2
 Wed = 3
+2


a source to share


4 answers


Try the 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

+9


a source


If you just have a non-datetime field with only Mon, Tue, Wed, etc., you can use STR_TO_DATE()

and WEEKDAY()

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 |
+-----------+
+4


a source


If your field is a date field then just use

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

More information on MySQL DATE_FORMAT can be found here .

+1


a source 


DAYOFWEEK(date);

http://www.tutorialspoint.com/mysql/mysql-date-time-functions.htm#function_dayofweek

+1


a source






More articles:


All Articles