mysql - Laravel Eloquent 地从字符串中获取日期格式

Question

在我的表格中，我将列强制为字符串(例如:“20170502”)。
我想从这个字符串中获取一天中的星期，所以我尝试转换为日期格式，但不起作用。

$event = VEventMp::where('event_seq', '=', $mypage_data->event_seq)
    ->select(
        'ENFORCE_PLACE',
        'ENFORCE_FROM',
        \DB::raw("DATE_FORMAT(STR_TO_DATE(ENFORCE_FROM,'%y/%m/%d'), '%m月%d日') 
         AS day"),
        \DB::raw('(CASE WHEN WEEKDAY(day) = 0 THEN "月"
        WHEN WEEKDAY(start_time) = 1 THEN "火"
        WHEN WEEKDAY(start_time) = 2 THEN "水"
        WHEN WEEKDAY(start_time) = 3 THEN "木"
        WHEN WEEKDAY(start_time) = 4 THEN "金"
        WHEN WEEKDAY(start_time) = 5 THEN "土"
        WHEN WEEKDAY(start_time) = 6 THEN "日" END) AS weekday')
    ->first();

我哪里出错了？
谢谢!

Answer 1

您有几个问题，首先，如果日期格式类似于 20170502，那么您必须在对 STR_TO_DATE 的调用中匹配该格式，因此将其更改为:

STR_TO_DATE(ENFORCE_FROM,'%Y%m%d')

其次，您不能在查询的 SELECT 部分使用别名，因此您需要将 CASE 表达式更改为

(CASE WHEN WEEKDAY(STR_TO_DATE(ENFORCE_FROM,'%Y%m%d')) = 0 THEN "月"
      WHEN WEEKDAY(STR_TO_DATE(ENFORCE_FROM,'%Y%m%d')) = 1 THEN "火"
      WHEN WEEKDAY(STR_TO_DATE(ENFORCE_FROM,'%Y%m%d')) = 2 THEN "水"
      WHEN WEEKDAY(STR_TO_DATE(ENFORCE_FROM,'%Y%m%d')) = 3 THEN "木"
      WHEN WEEKDAY(STR_TO_DATE(ENFORCE_FROM,'%Y%m%d')) = 4 THEN "金"
      WHEN WEEKDAY(STR_TO_DATE(ENFORCE_FROM,'%Y%m%d')) = 5 THEN "土"
      WHEN WEEKDAY(STR_TO_DATE(ENFORCE_FROM,'%Y%m%d')) = 6 THEN "日" END) AS weekday