我正试图尽可能地标准化ANSI SQL，并避免专有的供应商非标准实现。
ANSI SQL92 standard中没有指定将DATETIME数据类型格式化为字符串的函数。
最简单的解决方案是使用Oracle为此提供的函数：

SELECT TO_CHAR( yourdate, 'YYYYMM' ) FROM yourtable;

但是，您可以使用EXTRACT函数（在ANSI标准中）获得年和月组件：

SELECT EXTRACT( YEAR FROM yourdate ),
       EXTRACT( MONTH FROM yourdate )
FROM   yourtable;

然后你需要将数字转换为字符串并连接字符串：

SELECT TO_CHAR( EXTRACT( YEAR FROM yourdate ) )
       || TO_CHAR( EXTRACT( MONTH FROM yourdate ) )
FROM   yourtable

但是你试图避免TO_CHAR，所以你可以这样做：

SELECT CAST( EXTRACT( YEAR FROM yourdate ) AS VARCHAR2(4) )
       || CAST( EXTRACT( MONTH FROM yourdate ) AS VARCHAR2(2) )
FROM   yourtable

或者，使用隐式强制转换

SELECT EXTRACT( YEAR FROM yourdate )
       || EXTRACT( MONTH FROM yourdate )
FROM   yourtable

但是，如果年份不是4位数或月份不是2位数，则需要填充这些值;同样，简单的解决方案是TO_CHAR：

SELECT TO_CHAR( EXTRACT( YEAR FROM yourdate ), 'FM0000' )
       || TO_CHAR( EXTRACT( MONTH FROM yourdate ), 'FM00' )
FROM   yourtable

或LPAD：

SELECT LPAD( EXTRACT( YEAR FROM yourdate ), 4, '0' )
       || LPAD( EXTRACT( MONTH FROM yourdate ), 4, '0' )
FROM   yourtable

但是这两个都不在ANSI标准中，所以：

SELECT CASE
         WHEN EXTRACT( YEAR FROM yourdate ) < 10 THEN '000'
         WHEN EXTRACT( YEAR FROM yourdate ) < 100 THEN '00'
         WHEN EXTRACT( YEAR FROM yourdate ) < 1000 THEN '0'
         ELSE NULL
       END
       || EXTRACT( YEAR FROM yourdate )
       || CASE
         WHEN EXTRACT( MONTH FROM yourdate ) < 10 THEN '0'
       END
       || EXTRACT( MONTH FROM yourdate )
FROM   yourtable;

我们已经成功地将单个Oracle函数转换为ANSI兼容表达式的庞然大物。
但是，Oracle的DATE数据类型不符合ANSI标准（它是ANSI DATE和TIME数据类型的组合），所以我会问这样做是否值得-特别是如果您考虑显示日期的时间部分（除非您首先使用CAST将DATE转换为TIMESTAMP，否则EXTRACT不会提取该值）。

SELECT TO_CHAR( yourdate, 'YYYYMMDDHH24MISS' ) FROM yourtable

或

SELECT CASE
         WHEN EXTRACT( YEAR FROM yourdate ) < 10 THEN '000'
         WHEN EXTRACT( YEAR FROM yourdate ) < 100 THEN '00'
         WHEN EXTRACT( YEAR FROM yourdate ) < 1000 THEN '0'
         ELSE NULL
       END
       || EXTRACT( YEAR FROM yourdate )
       || CASE
         WHEN EXTRACT( MONTH FROM yourdate ) < 10 THEN '0'
       END
       || EXTRACT( MONTH FROM yourdate )
       || CASE
         WHEN EXTRACT( DAY FROM yourdate ) < 10 THEN '0'
       END
       || EXTRACT( DAY FROM yourdate )
       || CASE
         WHEN EXTRACT( HOUR FROM CAST( yourdate AS TIMESTAMP ) ) < 10 THEN '0'
       END
       || EXTRACT( HOUR FROM CAST( yourdate AS TIMESTAMP ) )
       || CASE
         WHEN EXTRACT( MINUTE FROM CAST( yourdate AS TIMESTAMP ) ) < 10 THEN '0'
       END
       || EXTRACT( MINUTE FROM CAST( yourdate AS TIMESTAMP ) )
       || CASE
         WHEN EXTRACT( SECOND FROM CAST( yourdate AS TIMESTAMP ) ) < 10 THEN '0'
       END
       || EXTRACT( SECOND FROM CAST( yourdate AS TIMESTAMP ) )
FROM   yourtable;

**[TL/DR]**使用TO_CHAR即可

这可能会帮助你：

SELECT extract(year from CURRENT_DATE) || case when extract(month from CURRENT_DATE) <10 THEN '0' || extract(month from CURRENT_DATE) END  THE_DATE from DUAL;