Oracle SQL：如何显示min(to_char(出生日期，'MM/DD/YYYY'))之后的最早日期

Question

我需要将表中的出生日期转换为"MM/DD/YYYY"格式，当我这样做时，最小(出生日期(只是月份是最小数字的日期，最大(出生日期(是月份是最大数字的日期。如何使其显示年份最小和最大的日期，但仍采用所需的日期格式？

Answer 1

只需使用以下

with t(birthdate) as
(
select trunc(sysdate) as birthdate from dual union all    
select trunc(sysdate)-300 from dual union all    
select trunc(sysdate)-600 from dual union all
select trunc(sysdate)-900 from dual
)
select to_char(birthdate,'MM/DD/YYYY') as birthdate
from t;
BIRTHDATE
----------
09/16/2018
11/20/2017
01/24/2017
03/30/2016

with t(birthdate) as
(
select trunc(sysdate) as birthdate from dual union all    
select trunc(sysdate)-300 from dual union all    
select trunc(sysdate)-600 from dual union all
select trunc(sysdate)-900 from dual
)
select to_char(min(birthdate),'MM/DD/YYYY') as min_birthdate,
to_char(max(birthdate),'MM/DD/YYYY')  as max_birthdate
from t;
MIN_BIRTHDATE   MAX_BIRTHDATE
-------------   -------------
03/30/2016      09/16/2018

首先应用最小/最大转换，然后根据所需的显示样式进行格式化。

Answer 2

您似乎正在查询min(to_char(birthdate, 'MM/DD/YYYY')).如您所见，当您这样做时，您将获得通过字典排序计算的最小值。相反，您应该在应用最小值/最大值后应用格式：

SELECT TO_CHAR(MIN(birthdate), 'MM/DD/YYYY'), TO_CHAR(MAX(birthdate), 'MM/DD/YYYY')
FROM   mytable