如何使用预言机中的子字符串读取 YYYYMM 格式的日期值

Question

在我的表customer_records中有一列 StartDate 具有DDMMYYYYHHMMSS格式的值(列数据类型为 VARCHAR(。
我想按日期以YYYYMM格式从表组中提取数据。

我有以下工作查询：它将以MMYYYY格式提取数据。

SELECT /*+ PARALLEL(a , 8 ) */
          NVL (SUBSTR (StartDate, 3, 6), 'Total') "Year-Month",Started",
         TO_CHAR (COUNT (1), '999,999,999') "Customer Count",
         TO_CHAR (
              SUM (Usage)/60,
            '999,999,999')`enter code here`
            "Duration (M)"
    FROM customer_records a
   WHERE type='NEW'
          and customer_status=0 
GROUP BY ROLLUP (to_date(SUBSTR (StartDate, 3, 6),'MMYYYY'), 'Total')
ORDER BY 1;

但我希望日期格式采用YYYYMM格式。
我尝试了以下选项。但不工作，

SELECT /*+ PARALLEL(a , 8 ) */
        NVL (to_date(SUBSTR (StartDate, 3, 6),'MMYYYY'), 'Total') "Month Started",
         TO_CHAR (COUNT (1), '999,999,999') "Customer Count",
         TO_CHAR (
              SUM (Usage)/60,
            '999,999,999')
            "Duration (M)"
    FROM customer_records a
   WHERE type='NEW'
          and customer_status=0 
GROUP BY ROLLUP (to_date(SUBSTR (StartDate, 3, 6),'MMYYYY'), 'Total')
ORDER BY 1;

有人可以帮我编辑上面的查询以YYYYMM格式获取数据吗？

Answer 1

如果给定的开始日期是日期格式，那么下面的一个将很好地工作。

更新：正如您在评论中提到的，它不是 datw 格式，然后您首先将其转换为日期格式，然后使用to_char函数所需的 YYYYMM 格式

SELECT /*+ PARALLEL(a , 8 ) */
            NVL ( to_CHAR(to_date(substr(StartDate, 1,8),'DDMMYYYY'),'YYYYMM'),'Total') "Month Started",
             TO_CHAR (COUNT (1), '999,999,999') "Customer Count",
             TO_CHAR (
                  SUM (Usage)/60,
                '999,999,999')
                "Duration (M)"
        FROM customer_records a
       WHERE type='NEW'
              and customer_status=0 
    GROUP BY ROLLUP (to_CHAR(to_date(substr(StartDate, 1,8),'DDMMYYYY'),'YYYYMM'))
    ORDER BY 1;

Answer 2

你可以试试：：：

select TO_DATE(TO_CHAR(StartDate, 'MM/YYYY'), 'MM/YYYY') mmyyyyFormatDate from customer_records ;  --get the date in mm/yyyy format