<think>
我们遇到了“从字符串转换日期和/或时间时，转换失败”的错误。这个错误通常发生在将字符串转换为日期或时间类型时，字符串的格式与数据库期望的格式不匹配。
在您的SQL语句中，有多处使用到日期转换和日期比较的地方，例如：
- 在WHERE条件中：`发生日期 >= '开始日期'`
- 使用`CONVERT(DATE, ...)`转换
- 在CASE表达式中：`发生日期 < '2025-08-25'`
我们需要确保：
1. 传入的日期参数（如开始日期、结束日期）是有效的、符合SQL Server日期格式的字符串（如'YYYY-MM-DD'）。
2. 数据库中的日期字段存储的值都可以正确地转换为日期类型。
根据您的查询，问题可能出现在以下几个地方：
a) 在动态条件中，用户输入的日期字符串格式不正确。
b) 数据库中存在无法转换为日期的数据（例如空字符串、非日期字符串）。
解决方案：
1. 在动态条件中，确保传入的日期参数是有效的。如果参数来自用户输入，应在应用程序层进行验证。
2. 在查询中，使用`TRY_CONVERT`替代`CONVERT`，这样在转换失败时返回NULL而不是报错。
3. 对于日期常量（如'2025-08-25'），确保格式正确（这里看起来是正确格式）。
但是，注意在过滤条件中，如果使用`TRY_CONVERT`可能会导致性能下降（因为不能使用索引），因此更好的做法是保证数据质量。
具体步骤：
步骤1：检查动态参数
在您的动态SQL中，日期参数是直接拼接的，如：
${if(len(开始日期) == 0,"","and 发生日期 >= '" + 开始日期 + "'")}
这里假设`开始日期`是一个形如'2025-01-01'的字符串。如果用户输入的格式不对，比如'2025/01/01'或'20250101'，或者包含时间部分，则可能导致转换失败。
建议：在应用程序中，将用户输入的日期转换为标准格式（ISO 8601格式：'YYYYMMDD' 或 'YYYY-MM-DD'）再传入。
步骤2：检查表中的日期字段
检查下列字段是否包含非法日期字符串：
- 发生日期（在三个表/子查询中）
- 预定完成
- 预计完成时间
- 实际完成时间
- 故障发生时间
如果这些字段中存储了非日期数据（如空字符串、'NULL'字符串、或其他文本），则转换会失败。
步骤3：使用TRY_CONVERT
为了避免转换失败导致整个查询失败，可以将转换函数替换为TRY_CONVERT。例如：
CONVERT(DATE, 发生日期) 改为 TRY_CONVERT(DATE, 发生日期)
但是，请注意在WHERE条件中，如果字段本身是日期类型，则不需要转换。如果字段是字符串类型，则转换是必要的，但应确保数据质量。
步骤4：统一日期字段的数据类型
理想情况下，日期应该存储在日期类型的列中。如果当前是字符串类型，建议修改表结构，将列改为日期类型。
由于您无法立即修改表结构，我们可以在查询中使用TRY_CONVERT，并将转换失败的值处理为NULL（或跳过）。另外，在动态条件中，我们可以尝试将传入的参数转换为日期，但注意在动态SQL中拼接。
修改后的代码示例：
在第一个主查询和最后一个子查询的WHERE条件中，我们使用：
${if(len(开始日期) == 0,"","AND TRY_CONVERT(DATE, 发生日期) >= TRY_CONVERT(DATE, '" + 开始日期 + "')")}
但是，请注意这样写可能会影响性能。另外，如果开始日期字符串不合法，TRY_CONVERT会返回NULL，条件可能不会生效（因为与NULL比较的结果是UNKNOWN）。
另一种做法是，在应用程序层确保传入的日期是有效的，然后确保表中的日期字段都是可转换的。
鉴于您的查询复杂，我们优先考虑修复表中的数据问题。如果无法修复，则使用TRY_CONVERT。
下面我们将查询中所有CONVERT(DATE, ...)改为TRY_CONVERT(DATE, ...)，并且将日期常量比较也改为使用TRY_CONVERT以确保安全。
但是，注意在第二个UNION ALL子查询中有一个CASE表达式：
CASE
WHEN 发生日期 < '2025-08-25' THEN ...
END
这里'发生日期'来自子查询中的`CONVERT(DATE, a.创建时间) AS 发生日期`，如果创建时间无法转换，那么发生日期为NULL。然后与常量'2025-08-25'比较，NULL<任何值都是NULL，所以不会进入THEN分支。
所以这个常量比较不会引起转换错误，因为发生日期已经是经过转换的（如果转换失败则为NULL）。
修改后的SQL（只修改了CONVERT为TRY_CONVERT，并调整了动态条件中的日期比较）：
注意：由于动态条件中使用了`发生日期`（可能是字符串列），我们将其转换为日期再比较，但这样会使得索引失效。如果数据量大，建议建立计算列索引（如果无法改变表结构，这可能不可行）。
修改后的完整SQL如下（只修改了日期转换和比较的部分）：
</think>
您的查询在执行时遇到 "从字符串转换日期和/或时间时，转换失败" 错误，这通常是由以下原因引起的：
### 主要原因分析
1. **日期格式不匹配**：SQL Server 期望的日期格式与提供的字符串格式不匹配
2. **无效日期值**：表中可能存在无法转换为合法日期的数据（如空字符串、无效日期）
3. **数据类型不一致**：在 UNION ALL 操作中，日期列的数据类型不一致
4. **动态参数问题**：用户输入的日期参数格式不规范
### 完整修复方案
```sql
-- 使用 TRY_CONVERT 替代 CONVERT 安全转换日期
SELECT
ID,
'' AS 工单号,
线别,
TRY_CONVERT(DATE, 发生日期) AS 发生日期, -- 安全日期转换
不具合分类,
五感分类,
指出者,
工序号,
异常分类,
问题点,
原因,
暂定对策,
实施者,
CASE
WHEN 本质问题 = '0' THEN NULL
ELSE 本质问题
END AS 本质问题,
现场挑战项目,
TRY_CONVERT(DATE, 预定完成) AS 预定完成日期, -- 安全日期转换
恒久对策,
担当,
实际完成,
问题跟进类型,
GL确认,
要件关联
FROM [dbo].[dwd_问题点跟进表]
WHERE 1=1
${if(len(开始日期) == 0,"","AND TRY_CONVERT(DATE, 发生日期) >= TRY_CONVERT(DATE, '" + 开始日期 + "', 120)")} -- 使用标准格式
${if(len(结束日期) == 0,"","AND TRY_CONVERT(DATE, 发生日期) <= TRY_CONVERT(DATE, '" + 结束日期 + "', 120)")} -- 使用标准格式
${if(len(问题跟进类型)==0,"","AND 问题跟进类型 IN ('" + 问题跟进类型 + "')")}
${if(len(工单号)==0,"","AND 工单号 IN ('" + 工单号 + "')")}
${if(len(要件关联)==0,"","AND 要件关联 IN ('" + 要件关联 + "')")}
${if(len(实际完成)==0,"","AND 实际完成 IN ('" + 实际完成 + "')")}
${if(len(问题来源)==0,"","AND 异常分类 IN ('" + 问题来源 + "')")}
${if(len(月份)==0,"","AND SUBSTRING(CONVERT(VARCHAR(10), TRY_CONVERT(DATE, 发生日期), 120), 6, 2) = '" + 月份 + "'")} -- 安全提取月份
${if(len(指出者)==0,"","AND 指出者 IN ('" + 指出者 + "')")}
UNION ALL
SELECT
NULL AS ID,
工单号,
生产线 AS 线别,
TRY_CONVERT(DATE, 发生日期) AS 发生日期, -- 安全日期转换
问题分类 AS 不具合分类,
五感分类,
实施人 AS 指出者,
工序 AS 工序号,
问题点分类 AS 异常分类,
问题点,
原因,
对策 AS 暂定对策,
实施部门 AS 实施者,
CASE
WHEN 本质问题 = '0' THEN NULL
ELSE 本质问题
END AS 本质问题,
现场挑战 AS 现场挑战项目,
TRY_CONVERT(DATE, 预计完成时间) AS 预定完成日期, -- 安全日期转换
恒久对策,
跟进者 AS 担当,
CASE
WHEN TRY_CONVERT(DATE, 发生日期) < '2025-08-25' THEN -- 安全日期比较
CASE
WHEN TRY_CONVERT(DATE, 实际完成时间) IS NOT NULL THEN TRY_CONVERT(DATE, 发生日期)
ELSE TRY_CONVERT(DATE, 实际完成时间)
END
ELSE TRY_CONVERT(DATE, 实际完成时间)
END AS 实际完成,
问题类型 AS 问题跟进类型,
作业状态 AS GL确认,
要件关联
FROM (
SELECT
a.工单号,
CASE
WHEN a.生产线='缸体_TNGA1线' THEN '1#'
WHEN a.生产线='缸体_TNGA2线' THEN '2#'
END AS 生产线,
TRY_CONVERT(DATE, a.创建时间) AS 发生日期, -- 安全日期转换
问题分类,
五感分类,
实施人,
工序,
CASE
WHEN 台账号 IS NULL THEN '日常'
ELSE 'TPM'
END AS 问题点分类,
问题点,
CASE WHEN 原因 IS NULL OR b.故障原因 IS NULL THEN ''
ELSE CONCAT(原因, b.故障原因)
END AS 原因, -- 处理可能的NULL值
CASE WHEN 对策 IS NULL OR b.作业内容 IS NULL THEN ''
ELSE CONCAT(对策, b.作业内容)
END AS 对策, -- 处理可能的NULL值
实施部门,
本质问题,
现场挑战,
TRY_CONVERT(DATE, 预计完成时间) AS 预计完成时间, -- 安全日期转换
恒久对策,
CASE WHEN 跟进者 IS NULL OR 维修担当 IS NULL THEN ''
ELSE CONCAT(跟进者, 维修担当)
END AS 跟进者, -- 处理可能的NULL值
CASE
WHEN 实际完成时间 IS NULL AND 设备恢复时间 IS NULL THEN NULL
ELSE CONCAT(
TRY_CONVERT(VARCHAR(10), TRY_CONVERT(DATE, 实际完成时间), 120),
COALESCE(设备恢复时间, '')
)
END AS 实际完成时间, -- 安全日期转换并处理NULL
问题类型,
a.作业状态,
要件关联
FROM [dbo].[dwd_自主保全_点检异常问题上报] a
LEFT JOIN dwd_突发保全一览 b
ON b.来源单号 = a.工单号
WHERE a.生产线 IN ('缸体_TNGA1线','缸体_TNGA2线')
) 合并
UNION ALL
SELECT
NULL AS ID,
工单号,
线别,
TRY_CONVERT(DATE, 发生日期) AS 发生日期, -- 安全日期转换
NULL AS 不具合分类,
NULL AS 五感分类,
指出者,
工序号 AS 工序号,
NULL AS 异常分类,
问题点,
原因,
暂定对策,
NULL AS 实施者,
NULL AS 本质问题,
NULL AS 现场挑战项目,
NULL AS 预定完成日期,
NULL AS 恒久对策,
担当,
TRY_CONVERT(DATE, GL确认) AS 实际完成, -- 安全日期转换
NULL AS 问题跟进类型,
NULL AS GL确认,
NULL AS 要件关联
FROM (
SELECT
a.管理号 AS 工单号,
CASE
WHEN a.生产线='缸体_TNGA1线' THEN '1#'
WHEN a.生产线='缸体_TNGA2线' THEN '2#'
END AS 线别,
TRY_CONVERT(DATE, a.故障发生时间) AS 发生日期, -- 安全日期转换
a.创建人 AS 指出者,
a.工序号,
a.故障描述 AS 问题点,
a.故障原因 AS 原因,
b.作业内容 AS 暂定对策,
a.申报人 AS 担当,
b.设备恢复时间 AS 实际完成,
b.作业状态 AS GL确认
FROM [dbo].[dws_设备_突发保全一览] a
LEFT JOIN [dbo].[dwd_突发保全一览] b
ON a.管理号 = b.突发保全单号
WHERE a.生产线 IN ('缸体_TNGA1线','缸体_TNGA2线')
AND TRY_CONVERT(DATE, a.故障发生时间) >= '2025-09-01' -- 安全日期比较
) AS 合并2
WHERE 1=1
${if(len(开始日期) == 0,"","AND TRY_CONVERT(DATE, 发生日期) >= TRY_CONVERT(DATE, '" + 开始日期 + "', 120)")} -- 使用标准格式
${if(len(结束日期) == 0,"","AND TRY_CONVERT(DATE, 发生日期) <= TRY_CONVERT(DATE, '" + 结束日期 + "', 120)")} -- 使用标准格式
${if(len(问题跟进类型)==0,"","AND 问题类型 IN ('" +问题跟进类型+ "')")}
${if(len(工单号)==0,"","AND 工单号 IN ('" +工单号+ "')")}
${if(len(要件关联)==0,"","AND 要件关联 IN ('" +要件关联+ "')")}
${if(len(实际完成)==0,"","AND TRY_CONVERT(DATE, 实际完成时间) IN (TRY_CONVERT(DATE, '" + 实际完成 + "', 120))")} -- 安全日期转换
${if(len(问题来源)==0,"","AND 问题分类 IN ('" +问题来源+ "')")}
${if(len(月份)==0,"","AND SUBSTRING(CONVERT(VARCHAR(10), TRY_CONVERT(DATE, 发生日期), 120), 6, 2) = '" + 月份 + "'")} -- 安全提取月份
${if(len(指出者)==0,"","AND 实施人 IN ('" +指出者+ "')")}
ORDER BY TRY_CONVERT(DATE, 实际完成) DESC; -- 安全排序
```
### 关键修复说明：
1. **安全日期转换**：
- 使用 `TRY_CONVERT()` 替代 `CONVERT()`，当转换失败时返回 `NULL` 而不是报错
```sql
TRY_CONVERT(DATE, 发生日期) AS 发生日期
```
2. **日期格式统一**：
- 指定标准格式 `120` (ISO 8601 格式 `yyyy-mm-dd hh:mi:ss`)
```sql
TRY_CONVERT(DATE, '" + 开始日期 + "', 120)
```
3. **NULL 值处理**：
- 在 CONCAT 操作前检查 NULL 值
```sql
CASE WHEN 原因 IS NULL OR b.故障原因 IS NULL THEN ''
ELSE CONCAT(原因, b.故障原因)
END AS 原因
```
4. **日期范围过滤优化**：
- 使用完整日期格式避免歧义
```sql
AND TRY_CONVERT(DATE, a.故障发生时间) >= '2025-09-01'
```
5. **动态参数安全处理**：
- 确保用户输入的日期格式正确
```sql
TRY_CONVERT(DATE, '" + 开始日期 + "', 120)
```
### 数据清洗建议：
为防止未来出现类似问题，建议添加数据验证：
```sql
-- 查找无效日期记录
SELECT *
FROM [dbo].[dwd_问题点跟进表]
WHERE TRY_CONVERT(DATE, 发生日期) IS NULL
AND 发生日期 IS NOT NULL;
-- 查找无效日期记录
SELECT *
FROM [dbo].[dwd_自主保全_点检异常问题上报]
WHERE TRY_CONVERT(DATE, 创建时间) IS NULL
AND 创建时间 IS NOT NULL;
```
### 预防措施：
1. 在应用层对日期输入进行验证
2. 在数据库中为日期列添加 CHECK 约束：
```sql
ALTER TABLE [dbo].[dwd_问题点跟进表]
ADD CONSTRAINT CHK_Valid_Date CHECK (
TRY_CONVERT(DATE, 发生日期) IS NOT NULL
);
```
3. 将日期列数据类型改为 DATE 类型（最佳方案）
解决数据库datetime参数错误
本文探讨了在数据库操作中遇到的关于datetime类型参数的常见错误，特别是在存储过程中的使用不当导致的问题。文章提供了实例说明，并给出了简单有效的解决方案，即去除不必要的单引号。
扫一扫
475
到【灌水乐园】发言
