查询字符类型转换date类型后值的函数

CREATE OR REPLACE FUNCTION get_internal_value(
    p_value IN VARCHAR2)
  RETURN VARCHAR2
IS
  temp_n NUMBER := 0;
BEGIN
  FOR i IN 1..15
  LOOP
    temp_n := temp_n + POWER(256, 15 - i) * ASCII(SUBSTR(RPAD(p_value, 15, CHR(
    0)), i, 1));
  END LOOP;
RETURN TO_CHAR(ROUND(temp_n, -21));
EXCEPTION
WHEN OTHERS THEN
  RETURN p_value;
END get_internal_value;
/
CREATE OR REPLACE FUNCTION get_external_value(
  p_value IN VARCHAR2)
RETURN VARCHAR2
IS
  temp_n    NUMBER;
  temp_i    INTEGER;
  my_result VARCHAR2(32767) := NULL;
BEGIN
  IF LENGTH(p_value) <> 36 OR SUBSTR(p_value, 16) <> '000000000000000000000' OR
    p_value           > POWER(256, 15) OR p_value < POWER(256, 14) THEN
    RETURN p_value; -- cannot get external value
  END IF;
  temp_n := p_value / POWER(256, 14); -- get most significant digits
  -- decoding most significant digits then shift multiplying by 256
  FOR i IN 1..14
  LOOP
    temp_i := TRUNC(temp_n);
    temp_n := (temp_n - temp_i) * 256;
    IF temp_i NOT BETWEEN 32 AND 126 OR temp_n NOT BETWEEN 32 AND 126 THEN
      EXIT; -- reached the tail
    END IF;
    my_result := my_result||CHR(temp_i); -- all but last byte
  END LOOP;
  IF temp_i NOT BETWEEN 32 AND 126 THEN
    RETURN my_result||'?'; -- cannot decode
  END IF;
  -- scan to the right starting at temp_i
  FOR i IN temp_i..126
  LOOP
    IF get_internal_value(my_result||CHR(i)) = p_value THEN
      RETURN my_result||CHR(i); -- approximate value
    END IF;
  END LOOP;
  -- scan to the left starting at temp_i
  FOR i IN 32..temp_i
  LOOP
    IF get_internal_value(my_result||CHR(temp_i + 32 - i)) = p_value THEN
      RETURN my_result||CHR(temp_i              + 32 - i); -- approximate value
    END IF;
  END LOOP;
  RETURN my_result||CHR(temp_i); -- this is the best we could do
EXCEPTION
WHEN OTHERS THEN
  RETURN p_value;
END get_external_value;
/
      

用法：

select get_internal_value('2022-05-31') a,get_internal_value('2022-06-02') b from dual;
A                                     B
---------------------------------------------------------------------------------
260592375524722000000000000000000000  260592375524722000000000000000000000      

引用自：