如何在Datetime查询中隐藏/移除年份，仅保留月日？

需求：从Datetime类型的created_date列返回MM-DD格式的结果，需将月和日合并在同一列中。

• 示例输入日期：2022-01-09
• 期望输出日期：01-09

当前使用的查询语句 #

Select Cast(created_date as date)Date, 
    [Name],
    count(Name)Total, 
    Cast(Sum([Total_Tax_Exclusive_Price])as Decimal(18,2))Revenue
From DBO.Receipts
Where [name] Like 'Smartfruit Smoothie%'
and [Created_Date] between '2022-09-15' and '2022-12-20'
Group by Cast(created_date as date),Name

当前查询返回结果 #

解决方案 #

针对SQL Server环境，提供两种可行修改方式：

方式1：使用FORMAT函数（SQL Server 2012及以上版本支持）

FORMAT函数可直接按指定格式输出日期字符串，写法简洁：

SELECT 
    FORMAT(Cast(created_date as date), 'MM-dd') AS Date, 
    [Name],
    COUNT(Name) AS Total, 
    CAST(SUM([Total_Tax_Exclusive_Price]) AS Decimal(18,2)) AS Revenue
FROM DBO.Receipts
WHERE [name] LIKE 'Smartfruit Smoothie%'
  AND [Created_Date] BETWEEN '2022-09-15' AND '2022-12-20'
GROUP BY Cast(created_date as date), Name

方式2：字符串拼接补零（兼容所有SQL Server版本）

通过RIGHT函数补零，保证月、日始终为两位数字，兼容性更强：

SELECT 
    RIGHT('0' + CAST(MONTH(created_date) AS VARCHAR(2)), 2) + '-' + 
    RIGHT('0' + CAST(DAY(created_date) AS VARCHAR(2)), 2) AS Date,
    [Name],
    COUNT(Name) AS Total, 
    CAST(SUM([Total_Tax_Exclusive_Price]) AS Decimal(18,2)) AS Revenue
FROM DBO.Receipts
WHERE [name] LIKE 'Smartfruit Smoothie%'
  AND [Created_Date] BETWEEN '2022-09-15' AND '2022-12-20'
GROUP BY Cast(created_date as date), Name

说明：两种方式的GROUP BY子句都保留Cast(created_date as date)，确保同一天的数据正确分组（即使不同年份的日期格式相同，也不会被错误合并）。

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