如何在SQL查询输出中新增自定义日期格式化列

没问题！要实现你想要的自定义列，核心是用数据库自带的日期格式化函数把f02_requested_date转换成月份全称/年份的格式，完全不需要复杂的CASE语句哦。下面分几种常见数据库给你对应的解决方案：

MySQL/MariaDB 版本 #

用DATE_FORMAT()函数，其中%M代表月份全称，%Y代表4位年份，直接把格式化后的列加到SELECT语句里即可：

select 
    f02_ticket_number as 'Tickets', 
    f02_requested_date as 'Request date',
    DATE_FORMAT(f02_requested_date, '%M/%Y') as 'My added column'
from client_uli_zendesk_ticket 
left outer join co_customer on f02_cst_key=cst_key 
left outer join co_individual_ext on cst_key=ind_cst_key_ext 
left join co_individual on ind_cst_key_ext=ind_cst_key 
where f02_status<>'deleted'

SQL Server 版本 #

如果你用的是SQL Server 2012及以上，可以用FORMAT()函数直接生成目标格式；也可以用DATENAME()分别取月份和年份再拼接：

方法1：用FORMAT（更简洁） #

select 
    f02_ticket_number as 'Tickets', 
    f02_requested_date as 'Request date',
    FORMAT(f02_requested_date, 'MMMM/yyyy') as 'My added column'
from client_uli_zendesk_ticket 
left outer join co_customer on f02_cst_key=cst_key 
left outer join co_individual_ext on cst_key=ind_cst_key_ext 
left join co_individual on ind_cst_key_ext=ind_cst_key 
where f02_status<>'deleted'

方法2：用DATENAME拼接（兼容更早版本） #

select 
    f02_ticket_number as 'Tickets', 
    f02_requested_date as 'Request date',
    DATENAME(month, f02_requested_date) + '/' + DATENAME(year, f02_requested_date) as 'My added column'
from client_uli_zendesk_ticket 
left outer join co_customer on f02_cst_key=cst_key 
left outer join co_individual_ext on cst_key=ind_cst_key_ext 
left join co_individual on ind_cst_key_ext=ind_cst_key 
where f02_status<>'deleted'

Oracle 版本 #

用TO_CHAR()函数处理日期，注意Oracle的Month格式会自带尾部空格，需要用TRIM()去掉：

select 
    f02_ticket_number as "Tickets", 
    f02_requested_date as "Request date",
    TRIM(TO_CHAR(f02_requested_date, 'Month')) || '/' || TO_CHAR(f02_requested_date, 'YYYY') as "My added column"
from client_uli_zendesk_ticket 
left outer join co_customer on f02_cst_key=cst_key 
left outer join co_individual_ext on cst_key=ind_cst_key_ext 
left join co_individual on ind_cst_key_ext=ind_cst_key 
where f02_status<>'deleted'

补充说明 #

• 新增的My added column数据类型是字符串（VARCHAR/CHAR类），因为是格式化后的文本，完全符合你要的显示需求。
• 如果你的数据库不在上述列表里（比如PostgreSQL、SQLite），可以告诉我具体数据库类型，我再给你调整对应的写法~

内容的提问来源于stack exchange，提问作者user7948585