我想在sql里面实现 时间转换成班别 20:00到第二天8:00是早班 8:00到晚上20:00 是晚班 该怎么转换
如下面的例子由前面的字段生成后面的字段
表p1
time1 shit
8:20 早班
16:30 早班
20:50 晚班
21:20 晚班
5:30 早班
如下面的例子由前面的字段生成后面的字段
表p1
time1 shit
8:20 早班
16:30 早班
20:50 晚班
21:20 晚班
5:30 早班
SQL> SELECT * FROM TABLE1;TIME1
------------------------------
9/1/2008 8:20:00 AM
9/1/2008 4:30:00 PM
9/1/2008 8:50:00 PM
9/1/2008 9:20:00 PM
9/1/2008 5:30:00 AMSQL>
SQL> SELECT TIME1,
2 (CASE WHEN TIME1 BETWEEN TO_DATE(TO_CHAR(TIME1,'YYYY-MM-DD')||'20:00','YYYY-MM-DD HH24:MI') AND TO_DATE(TO_CHAR(TIME1+1,'YYYY-MM-DD')||'08:00','YYYY-MM-DD HH24:MI')
3 OR TIME1 BETWEEN TO_DATE(TO_CHAR(TIME1-1,'YYYY-MM-DD')||'20:00','YYYY-MM-DD HH24:MI') AND TO_DATE(TO_CHAR(TIME1,'YYYY-MM-DD')||'08:00','YYYY-MM-DD HH24:MI')
4 THEN 'Morning Shift'
5 WHEN TIME1 BETWEEN TO_DATE(TO_CHAR(TIME1,'YYYY-MM-DD')||'08:00','YYYY-MM-DD HH24:MI') AND TO_DATE(TO_CHAR(TIME1+1,'YYYY-MM-DD')||'20:00','YYYY-MM-DD HH24:MI')
6 OR TIME1 BETWEEN TO_DATE(TO_CHAR(TIME1-1,'YYYY-MM-DD')||'08:00','YYYY-MM-DD HH24:MI') AND TO_DATE(TO_CHAR(TIME1,'YYYY-MM-DD')||'20:00','YYYY-MM-DD HH24:MI')
7 THEN 'Afternoon Shift'
8 END) "SHIFT"
9 FROM TABLE1;TIME1 SHIFT
------------------------------ ---------------
9/1/2008 8:20:00 AM Afternoon Shift
9/1/2008 4:30:00 PM Afternoon Shift
9/1/2008 8:50:00 PM Morning Shift
9/1/2008 9:20:00 PM Morning Shift
9/1/2008 5:30:00 AM Morning ShiftSQL>