sql - SQL:如何解决“从字符串转换日期和/或时间时转换失败”?
问题描述
我想为我的 HRMS 出勤报告使用交叉表。当我使用这个 SQL 脚本时,我遇到了这个执行错误说
“(受影响的 30 行)
消息 241,级别 16,状态 1,第 47 行
从字符串转换日期和/或时间时转换失败"
SQL脚本:
USE [KCLDB]
GO
/****** Object: StoredProcedure [hrms].[GET_ATTENDANCEREPORT] Script Date: 05-Jul-18 10:41:42 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [hrms].[GET_ATTENDANCEREPORT]
@STARTDATE DATE,
@ENDDATE DATE
AS
BEGIN
WITH DATERANGE AS
(
SELECT
DT = DATEADD(DD, 0, @STARTDATE)
WHERE
DATEADD(DD, 1, @STARTDATE) <= @ENDDATE
UNION ALL
SELECT DATEADD(DD, 1, DT)
FROM DATERANGE
WHERE DATEADD(DD, 1, DT) <= @ENDDATE
)
SELECT *
INTO #TMP_DATES
FROM DATERANGE
DECLARE @COLUMN VARCHAR(MAX)
SELECT @COLUMN = ISNULL(@COLUMN + ',', '') + '[' + CAST(CONVERT(DATE , T.DT) AS VARCHAR) + ']'
FROM #TMP_DATES T
DECLARE @Columns2 VARCHAR(MAX)
SET @Columns2 = SUBSTRING((SELECT DISTINCT ',ISNULL(['+ CAST(CONVERT(DATE , DT) as varchar )+'],''N/A'') AS ['+CAST(CONVERT(DATE , DT) as varchar )+']' FROM #TMP_DATES GROUP BY DT FOR XML PATH('')),2,8000)
DECLARE @QUERY VARCHAR(MAX)
SET @QUERY = 'SELECT UserID, ' + @Columns2 +' FROM
(
SELECT A.UserID , B.DT AS DATE, A.[Clock], A.[Date] FROM hrms.AttendanceRecord A RIGHT OUTER JOIN #TMP_DATES B ON A.[Date]=B.DT
) X
PIVOT
(
MIN([Clock])
FOR [Date] IN (' + @COLUMN + ')
) P
WHERE ISNULL(UserID,'''')<>''''
'
EXEC (@QUERY)
DROP TABLE #TMP_DATES
END
---Execution Line
EXEC hrms.GET_ATTENDANCEREPORT @STARTDATE = '2018-05-01', @ENDDATE= '2018-05-30'
解决方案
这应该工作
ALTER PROCEDURE [hrms].[GET_ATTENDANCEREPORT]
@STARTDATE DATE
,@ENDDATE DATE
AS
BEGIN
WITH
[DATERANGE]
AS
(
SELECT
[DT] = DATEADD(DD, 0, @STARTDATE)
WHERE DATEADD(DD, 1, @STARTDATE) <= @ENDDATE
UNION ALL
SELECT
DATEADD(DD, 1, [DATERANGE].[DT])
FROM
[DATERANGE]
WHERE DATEADD(DD, 1, [DATERANGE].[DT]) <= @ENDDATE
)
SELECT
[DATERANGE].[DT]
INTO
[#TMP_DATES]
FROM
[DATERANGE]
DECLARE @COLUMN VARCHAR(MAX)
SELECT
@COLUMN = ISNULL(@COLUMN + ',', '') + '[' + CAST(CONVERT(DATE, [T].[DT]) AS VARCHAR) + ']'
FROM
[#TMP_DATES] AS [T]
DECLARE @Columns2 VARCHAR(MAX)
SET @Columns2 = SUBSTRING(
(
SELECT DISTINCT
',ISNULL(CONVERT(VARCHAR(20),[' + CAST(CONVERT(DATE, [DT]) AS VARCHAR) + '],120),''N/A'') AS ['
+ CAST(CONVERT(DATE, [DT]) AS VARCHAR) + ']'
FROM
[#TMP_DATES]
GROUP BY
[DT]
FOR XML PATH('')
)
,2
,8000
)
DECLARE @QUERY VARCHAR(MAX)
SET @QUERY = 'SELECT UserID, ' + @Columns2
+ ' FROM
(
SELECT A.UserID , B.DT AS DATE2, A.[Clock], A.[Date] FROM hrms.AttendanceRecord A RIGHT OUTER JOIN #TMP_DATES B ON A.[Date]=B.DT
) X
PIVOT
(
MIN([Clock])
FOR [Date] IN (' + @COLUMN + ')
) P
WHERE ISNULL(UserID,'''')<>''''
'
EXEC ( @QUERY )
DROP TABLE [#TMP_DATES]
END
GO
---Execution Line
EXEC dbo.GET_ATTENDANCEREPORT @STARTDATE = '2018-05-01', @ENDDATE= '2018-05-30'
问题是,由于列时钟是日期时间,因此枢轴中的所有列都是日期时间,如果它们为空,您试图用“N/A”替换它们。“N/A”不是有效的日期时间,因此出现错误。
推荐阅读
- javascript - JavaScript 数组重构
- python - excel保存过程中的字符不正确
- macos - 无法在 macOS 应用程序的 FFMPEG 进程中通过 NSOpenPanel 访问用户选择的文件
- parallel-processing - 循环依赖分析:这个循环可以并行化吗?
- php - 显示下一个字段悬停切换按钮
- python - 如何在python中执行两个大熊猫文件的交叉连接
- html - 为数组对象管理我的 *ngFor 时出现问题
- scala - 播放 Json 验证在日期字段上失败并出现错误:[error.expected.date.isoformat]
- react-native - 调用 SearchBox 组件进入事件并在子组件上填充数据
- angular - ngx剪贴板订阅copyResponse$不起作用