我的动态数据透视脚本中的数据类型不兼容

Question

所以我一直在开发一个动态透视脚本，我几乎已经完成了它的工作，但我在声明的变量方面遇到了问题。这是我的代码：

DECLARE @start_date DATE
DECLARE @end_date DATE
SET @start_date = CAST(DATEADD(dd,((DATEDIFF(dd,'17530101',GETDATE())/7)*7)-7,'17530101') AS DATE)
SET @end_date = CAST(DATEADD(dd,((DATEDIFF(dd,'17530101',GETDATE())/7)*7)-1,'17530101') AS DATE)
DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';
SELECT @cols = @cols + QUOTENAME(TicketDate) + ',' 
FROM 
(SELECT DISTINCT TOP 14 ad.TicketDate 
FROM AttendDet ad 
WHERE CAST(ad.TicketDate AS DATE) BETWEEN  @start_date AND @end_date 
AND ad.EmplCode IS NOT NULL 
ORDER BY ad.TicketDate) as dates
SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols)) 
SET @query =
'SELECT * FROM
(
SELECT
CAST(ad.EmplName AS NVARCHAR(MAX)) AS [EmplName],
CAST(ad.TicketDate AS DATE) AS [TicketDate],
ROUND(ad.TotActTime, 2) AS [TotalHrs]
FROM AttendDet ad
WHERE ad.EmplCode IS NOT NULL
AND ad.AttendCode <> 9999
AND CAST(ad.TicketDate AS DATE) BETWEEN (' + @start_date + ') AND (' + @end_date + ')
) basedata
PIVOT
(
SUM(TotalHrs) FOR TicketDate IN (' + @cols + ')
) piv'
EXECUTE(@query)

导致错误的线路是这样的：

AND CAST(ad.TicketDate AS DATE) BETWEEN (' + @start_date + ') AND (' + @end_date + ')

我得到的错误是："数据类型varchar和date在add运算符中不兼容。"我尝试将数据类型更改为varchar，但没有成功，当我这样做时，我得到了这个错误：从字符串转换日期和/或时间时转换失败。

我知道我的方向是正确的，因为如果我手动硬编码那一行，例如：

AND CAST(ad.TicketDate AS DATE) BETWEEN ''10/22/18'' AND ''10/28/18''

它的效果和预期的一样完美，但我显然不想这样做，因为它会破坏剧本的全部目的。只要设置了@start_data和@end_date，整个事情就应该正常工作，但我一直在思考如何完成这项工作。在过去的一个小时里，我一直在尝试各种各样的东西，但都搞不清楚

提前感谢

Answer 1

您需要将其强制转换为varchar，因为SQL在这里将+解释为加法与级联。

DECLARE @start_date DATE = getdate()
DECLARE @end_date DATE = getdate()
DECLARE @cols varchar(10) = ''
DECLARE @query varchar(max)
SET @query =
'SELECT * FROM
(
SELECT
CAST(ad.EmplName AS NVARCHAR(MAX)) AS [EmplName],
CAST(ad.TicketDate AS DATE) AS [TicketDate],
ROUND(ad.TotActTime, 2) AS [TotalHrs]
FROM AttendDet ad
WHERE ad.EmplCode IS NOT NULL
AND ad.AttendCode <> 9999
AND CAST(ad.TicketDate AS DATE) BETWEEN ''' + cast(@start_date as varchar) + ''' AND ''' + cast(@end_date as varchar) + '''
) basedata
PIVOT
(
SUM(TotalHrs) FOR TicketDate IN (' + @cols + ')
) piv'
print @query

所以你的查询会是这样的。注意，我把命令改为打印而不是执行，这样你就可以看到它的样子：

DECLARE @start_date DATE
DECLARE @end_date DATE
SET @start_date = CAST(DATEADD(dd,((DATEDIFF(dd,'17530101',GETDATE())/7)*7)-7,'17530101') AS DATE)
SET @end_date = CAST(DATEADD(dd,((DATEDIFF(dd,'17530101',GETDATE())/7)*7)-1,'17530101') AS DATE)
DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';
SELECT @cols = @cols + QUOTENAME(TicketDate) + ',' 
FROM 
(SELECT DISTINCT TOP 14 ad.TicketDate 
FROM AttendDet ad 
WHERE CAST(ad.TicketDate AS DATE) BETWEEN  @start_date AND @end_date 
AND ad.EmplCode IS NOT NULL 
ORDER BY ad.TicketDate) as dates
SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols)) 
SET @query =
'SELECT * FROM
(
SELECT
CAST(ad.EmplName AS NVARCHAR(MAX)) AS [EmplName],
CAST(ad.TicketDate AS DATE) AS [TicketDate],
ROUND(ad.TotActTime, 2) AS [TotalHrs]
FROM AttendDet ad
WHERE ad.EmplCode IS NOT NULL
AND ad.AttendCode <> 9999
AND CAST(ad.TicketDate AS DATE) BETWEEN ''' + cast(@start_date as varchar) + ''' AND ''' + cast(@end_date as varchar) + '''
) basedata
PIVOT
(
SUM(TotalHrs) FOR TicketDate IN (' + @cols + ')
) piv'
--EXECUTE(@query)
print @query

Answer 2

这应该有效：

AND CAST(ad.TicketDate AS DATE) BETWEEN (' + CONVERT(varchar(11),@start_date,101) + ') AND (' + CONVERT(varchar(11),@end_date,101) + ')

Answer 3

您有几个选项。

1. 使用+运算符连接字符串，所有值都必须是字符串本身，并且不能像错误消息所说的那样将DATE隐式转换为字符串。不过，您可以将两个日期变量都封装在显式CASTs中，或者如果是您的包，则封装在CONVERTs中：

   (' + CAST(@start_date AS VARCHAR(10)) + ') AND (' + CAST(@end_date AS VARCHAR(10)) + ')

2. 如果您的版本支持它(2012+(，只需使用CONCAT。它为您处理转换。

   SET @query = CONCAT( 'SELECT * FROM ( SELECT CAST(ad.EmplName AS NVARCHAR(MAX)) AS [EmplName], CAST(ad.TicketDate AS DATE) AS [TicketDate], ROUND(ad.TotActTime, 2) AS [TotalHrs] FROM AttendDet ad WHERE ad.EmplCode IS NOT NULL AND ad.AttendCode <> 9999 AND CAST(ad.TicketDate AS DATE) BETWEEN (', @start_date, ') AND (', @end_date, ') ) basedata PIVOT ( SUM(TotalHrs) FOR TicketDate IN (', @cols, ') ) piv' )