Case 时间转换

SELECT
  T_KOJIN_YOTEI."YOTEI" AS "YOTEI",
  T_KOJIN_YOTEI."BASHO" AS "BASHO",
  T_KOJIN_YOTEI."YOTEI_START_DT",
  CASE
      WHEN TO_CHAR(T_KOJIN_YOTEI."YOTEI_START_DT",'HH24:MI') = '07:30' THEN TO_DATE(REPLACE(TO_CHAR(T_KOJIN_YOTEI."YOTEI_START_DT",'YYYY/MM/DD HH24:MI:SS'),TO_CHAR(T_KOJIN_YOTEI."YOTEI_START_DT",'HH24:MI'),'08:30'),'YYYY/MM/DD HH24:MI:SS')
      WHEN TO_CHAR(T_KOJIN_YOTEI."YOTEI_START_DT",'HH24:MI') = '08:00' THEN TO_DATE(REPLACE(TO_CHAR(T_KOJIN_YOTEI."YOTEI_START_DT",'YYYY/MM/DD HH24:MI:SS'),TO_CHAR(T_KOJIN_YOTEI."YOTEI_START_DT",'HH24:MI'),'08:30'),'YYYY/MM/DD HH24:MI:SS')
      ELSE
      END "YOTEI_START_DT",
  T_KOJIN_YOTEI."YOTEI_END_DT" AS "YOTEI_END_DT",
  T_KAIIN."SYSTEM_NM" AS "SYSTEM_NM"
FROM
  T_KOJIN_YOTEI,
  T_KAIIN
WHERE
  T_KAIIN.USER_CD = T_KOJIN_YOTEI.USER_CD
  AND TO_DATE(T_KOJIN_YOTEI."YOTEI_START_DT") <= ?
  AND T_KOJIN_YOTEI.YOTEI_END_DT >= ?
  AND TO_CHAR(T_KOJIN_YOTEI."YOTEI_START_DT", 'HH24:MI:SS') BETWEEN '07:30:00' AND '19:30:00'
  AND TO_CHAR(T_KOJIN_YOTEI."YOTEI_END_DT", 'HH24:MI:SS') BETWEEN '07:30:00' AND '19:30:00'
ORDER BY
  TO_CHAR(T_KOJIN_YOTEI.YOTEI_START_DT,'HH24:MI')
 
 
**********************************************************************************************
SELECT
  T_KOJIN_YOTEI."YOTEI" AS "YOTEI",
  T_KOJIN_YOTEI."BASHO" AS "BASHO",
  T_KOJIN_YOTEI."YOTEI_START_DT" AS YOTEI_START_DT,
  T_KOJIN_YOTEI."YOTEI_END_DT" AS "YOTEI_END_DT",
  T_KAIIN."SYSTEM_NM" AS "SYSTEM_NM",
  CASE
    WHEN TO_CHAR(T_KOJIN_YOTEI."YOTEI_START_DT",'YYYY-MM-DD')= TO_CHAR(T_KOJIN_YOTEI."YOTEI_END_DT",'YYYY-MM-DD') THEN '0'
    WHEN '2008-01-02' = TO_CHAR(T_KOJIN_YOTEI."YOTEI_START_DT",'YYYY-MM-DD') AND '2008-01-02'= TO_CHAR(T_KOJIN_YOTEI."YOTEI_END_DT",'YYYY-MM-DD') THEN '0'
    WHEN '2008-01-02' = TO_CHAR(T_KOJIN_YOTEI."YOTEI_START_DT",'YYYY-MM-DD') THEN '3'
    WHEN '2008-01-02' = TO_CHAR(T_KOJIN_YOTEI."YOTEI_END_DT",'YYYY-MM-DD') THEN '2'
    WHEN '2008-01-02' BETWEEN TO_CHAR(T_KOJIN_YOTEI."YOTEI_START_DT",'YYYY-MM-DD') AND TO_CHAR(T_KOJIN_YOTEI."YOTEI_END_DT",'YYYY-MM-DD') THEN '1'
    END "FALG"
FROM
  T_KOJIN_YOTEI,
  T_KAIIN
WHERE
  T_KAIIN.USER_CD = T_KOJIN_YOTEI.USER_CD
  AND TO_DATE(T_KOJIN_YOTEI."YOTEI_START_DT") <= '2008-01-02'
  AND T_KOJIN_YOTEI.YOTEI_END_DT >= '2008-01-02'
  AND TO_CHAR(T_KOJIN_YOTEI."YOTEI_START_DT", 'HH24:MI:SS') BETWEEN '07:30:00' AND '19:30:00'
  AND TO_CHAR(T_KOJIN_YOTEI."YOTEI_END_DT", 'HH24:MI:SS') BETWEEN '07:30:00' AND '19:30:00'
ORDER BY
  TO_CHAR(T_KOJIN_YOTEI.YOTEI_START_DT,'HH24:MI'),
  T_KOJIN_YOTEI.KOJIN_YOTEI_SEQ DESC