날짜 값에 CEIL, FLOOR 함수 사용

Oracle 23c부터 DATE, TIMESTAMP, INTERVAL 등의 날짜 값에 CEIL, FLOOR 함수를 사용할 수 있습니다.

You can now pass DATE, TIMESTAMP, and INTERVAL values to the CEIL and FLOOR functions. These functions include an optional second argument to specify a rounding unit. You can also pass INTERVAL values to ROUND and TRUNC functions. These functions make it easy to find the upper and lower bounds for date and time values for a specified unit.

 

아래 쿼리는 DATE 값에 CEIL 함수를 사용합니다. 지정한 포맷에 따른 상한 값이 반환됩니다.

-- 1
SELECT c1
     , CEIL (c1, 'YY') AS yy
     , CEIL (c1, 'MM') AS mm
     , CEIL (c1, 'DD') AS dd
     , CEIL (c1, 'HH') AS hh
     , CEIL (c1, 'MI') AS mi
  FROM (SELECT TO_DATE ('2050-01-02 12:34:56', 'YYYY-MM-DD HH24:MI:SS') AS c1 FROM DUAL);

C1                  YYYY                MM                  DD                  HH                  MI
------------------- ------------------- ------------------- ------------------- ------------------- -------------------
2050-01-02 12:34:56 2051-01-01 00:00:00 2050-02-01 00:00:00 2050-01-03 00:00:00 2050-01-02 13:00:00 2050-01-02 12:35:00

1개의 행이 선택되었습니다.

 

아래 쿼리는 DATE 값에 FLOOR 함수를 사용합니다. 지정한 포맷에 따른 하한 값이 반환됩니다.

-- 2
SELECT c1
     , FLOOR (c1, 'YY') AS yy
     , FLOOR (c1, 'MM') AS mm
     , FLOOR (c1, 'DD') AS dd
     , FLOOR (c1, 'HH') AS hh
     , FLOOR (c1, 'MI') AS mi
  FROM (SELECT TO_DATE ('2050-01-02 12:34:56', 'YYYY-MM-DD HH24:MI:SS') AS c1 FROM DUAL);

C1                  YYYY                MM                  DD                  HH                  MI
------------------- ------------------- ------------------- ------------------- ------------------- -------------------
2050-01-02 12:34:56 2050-01-01 00:00:00 2050-01-01 00:00:00 2050-01-02 00:00:00 2050-01-02 12:00:00 2050-01-02 12:34:00

1개의 행이 선택되었습니다.