sql - 在字符串聚合函数期间更改 Oracle 日期值

Question

当我运行以下 2 个语句时，我得到的输出是

SELECT TO_DATE ('01/01/99', 'MM/DD/YYYY') AS date_a FROM DUAL
UNION ALL
SELECT TO_DATE ('01/01/99', 'MM/DD/YYYY') AS date_a FROM DUAL
Output
---------
1/1/0099
1/1/0099

现在 - 如果我尝试连接上述 2 个值而不使用 listagg 函数更改值，则输出会像这样更改。

SELECT listagg(date_a,'#') WITHIN GROUP(order by date_a)  FROM 
(
SELECT TO_DATE ('01/01/99', 'MM/DD/YYYY') AS date_a FROM DUAL
UNION ALL
SELECT TO_DATE ('01/01/99', 'MM/DD/YYYY') AS date_a FROM DUAL
)
Output
--------
01-JAN-99#01-JAN-99

我需要输出为1/1/00999#1/1/00999

请帮我解决这个问题

标签： sqloracleoracle11goracle12clistagg

Answer 1

你有隐式转换！

Listagg 接受字符输入。因此，数据库在您的日期值周围有一个隐含的 to_char。您可以通过更改 nls_date_format 参数看到这一点：

alter session set nls_date_format = 'dd-mon-yyyy';

select listagg(date_a,'#') within group(order by date_a)  dts
from 
(
select to_date ('01/01/99', 'mm/dd/yyyy') as date_a from dual
union all
select to_date ('01/01/99', 'mm/dd/yyyy') as date_a from dual
);

DTS                       
01-jan-0099#01-jan-0099  

alter session set nls_date_format = 'yyyy-mm-dd';

select listagg(date_a,'#') within group(order by date_a) dts
from 
(
select to_date ('01/01/99', 'mm/dd/yyyy') as date_a from dual
union all
select to_date ('01/01/99', 'mm/dd/yyyy') as date_a from dual
);

DTS                     
0099-01-01#0099-01-01 

通过明确 to_charing 您的日期来避免这种情况：

select listagg( to_char ( date_a, 'mm/dd/yyyy' ),'#') within group(order by date_a) dts 
from 
(
select to_date ('01/01/99', 'mm/dd/yyyy') as date_a from dual
union all
select to_date ('01/01/99', 'mm/dd/yyyy') as date_a from dual
);

DTS                     
01/01/0099#01/01/0099