如何将SQL查询中显示为1900-01-01的空日期值替换为空字符串

修改后的完整查询语句如下：

SELECT EquipmentOC.Код,
EquipmentPlace.Участок as Участок,
[EquipmentСategory].Категория as Категория,
EquipmentOC.Имя, EquipmentOC.[Инв Номер],  
IIF(EquipmentOC.[Дата Ввода в экспл] = '19000101', '', FORMAT (EquipmentOC.[Дата Ввода в экспл], 'dd.MM.yy')) as [Дата Ввода в экспл],
EquipmentOC.Примечание,
EquipmentOC.ППР, 
IIF(EquipmentOC.ТО = '19000101', '', FORMAT (EquipmentOC.ТО, 'dd.MM.yy')) as ТО,
IIF(EquipmentOC.МР = '19000101', '', FORMAT ( EquipmentOC.МР, 'dd.MM.yy')) as МР,
EquipmentClassification.Классификатор as Классификатор, 
EquipmentLocation.Локация as Локация
FROM ((([EquipmentOC]
INNER JOIN [EquipmentPlace] ON [EquipmentOC].Участок = [EquipmentPlace].Код)
INNER JOIN [EquipmentСategory] ON [EquipmentOC].Категория = EquipmentСategory.Код)
INNER JOIN [EquipmentClassification] ON [EquipmentOC].Классификатор = [EquipmentClassification].Код)
INNER JOIN [EquipmentLocation] ON [EquipmentOC].Локация = [EquipmentLocation].Код
ORDER BY EquipmentOC.ТО DESC 

方案说明 #

• 用'19000101'作为判断值是SQL Server通用的ISO标准日期写法，不受数据库日期格式配置影响，判断准确率更高
• IIF是SQL Server 2012及以上版本支持的简化条件判断函数，如果你使用的是更低版本，可以替换为CASE语句实现相同逻辑：

  CASE WHEN EquipmentOC.ТО = '19000101' THEN '' ELSE FORMAT(EquipmentOC.ТО, 'dd.MM.yy') END as ТО
  

• 排序直接使用原始日期字段即可，不需要额外做日期转换，性能更高也不会出现字符串排序错误
• 原查询末尾额外添加的单独CASE字段属于冗余内容，已经整合到对应格式化日期列的判断逻辑中，直接删除即可

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