从日期字符串中提取日期数字

发布于 2024-09-01 07:23:36 字数 116 浏览 5 评论 0原文

是否可以使用 SQL 语句从日期名称中提取整数值，即“Mon”、“Tue”、“Wed”？

例如：

 Mon = 1
 Tue = 2
 Wed = 3

原文

Is it possible to extract an integer value from day names, i.e. "Mon", Tue", "Wed" with an SQL statement?

For example:

 Mon = 1
 Tue = 2
 Wed = 3

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蓝天白云 2024-09-08 07:23:36

尝试字段：

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

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

回复收藏 0 原文

匿名的好友 2024-09-08 07:23:36

如果您只有一个只有周一、周二、周三等的非日期时间字段，则可以使用 STR_TO_DATE() 和 WEEKDAY() 函数得出如下内容：

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

测试用例：

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

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

回复收藏 0 原文