SQL Query有效，但它没有？ [英] SQL Query works, but it doesn't?
本文介绍了SQL Query有效，但它没有？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在这里有一个复杂的查询，它确定了两个事件之间的平均响应时间，并考虑了工作时间和周末。当我在Microsoft SQL Server Management Studio中运行它时它工作正常，但是当我从我的asp.net c＃网站运行它时，我收到错误'DATEPART'附近的语法不正确。有人有什么想法吗？
I've got a complex query going on here that figures out what the average response time between two events is, and takes into account working hours and weekends. It works fine when I run it in Microsoft SQL Server Management Studio, but I get the error "Incorrect Syntax near 'DATEPART'" when I run it from my asp.net c# website. Anybody have any ideas?
select
AVG(weekdiff) as 'Response Time'
from
(select
TicketID,
modifiedCreateDate,
AssignedDateTime,
DATEDIFF(hour,
modifiedcreatedate,
AssignedDateTime) as actualhours,
DATEDIFF(day,
modifiedcreatedate,
AssignedDateTime)actualdays,
DATEDIFF(hour,
modifiedcreatedate,
AssignedDateTime)-( DATEDIFF(day,
modifiedcreatedate,
AssignedDateTime)*13)- ((datediff(wk,
modifiedCreateDate,
AssignedDateTime) * 2) - case
when datepart(dw,
modifiedCreateDate) = 1 then 1
else 0
end + case
when datepart(dw,
AssignedDateTime) = 1 then 1
else 0
end)*11 as Weekdiff
FROM
(SELECT
[TicketID],
(CASE
WHEN DATEPART(hour,
OriginDateTime) > '17' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
(DATEADD (day,
1,
OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(month,
(DATEADD (day,
1,
OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(day,
(DATEADD (day,
1,
OriginDateTime))) AS varchar(4)) AS datetime))
WHEN DATEPART(hour,
OriginDateTime) < '6' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
OriginDateTime) AS varchar(4)) + '-' + CAST(DATEPART(month,
OriginDateTime) AS varchar(4)) + '-' + CAST(DATEPART(day,
OriginDateTime) AS varchar(4)) AS datetime))
WHEN DATEPART(dw,
OriginDateTime) = '7' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
(DATEADD (day,
2,
OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(month,
(DATEADD (day,
2,
OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(day,
(DATEADD (day,
2,
OriginDateTime))) AS varchar(4)) AS datetime))
WHEN DATEPART(dw,
OriginDateTime) = '1' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
(DATEADD (day,
1,
OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(month,
(DATEADD (day,
1,
OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(day,
(DATEADD (day,
1,
OriginDateTime))) AS varchar(4)) AS datetime))
ELSE OriginDateTime
end) AS modifiedCreateDate,
[AssignedDateTime]
FROM
[LFM_Archive].[dbo].[IT_Track]
WHERE
DATEDIFF(HOUR,[OriginDateTime],[AssignedDateTime]) IS NOT NULL)x
where
DATEPART(WEEK, [modifiedCreateDate]) = DATEPART(WEEK, getdate())
)y
编辑：
所以，我们尝试做一个视图，但是从LFM_Archive.dbo.Statistics运行查询SELECT AVG（workdaydiff）仍会抛出不正确的语法来自网页的DATEPART错误。以下是视图：
So, we tried doing a view instead, but running the query SELECT AVG(workdaydiff) from LFM_Archive.dbo.Statistics still throws the incorrect syntax near DATEPART error from the webpage. Here's the view:
SELECT ticketid,
modifiedcreatedate,
assigneddatetime,
Datediff(hour, modifiedcreatedate, assigneddatetime)
AS actualhours,
Datediff(day, modifiedcreatedate, assigneddatetime)
AS actualdays,
( Datediff(hour, modifiedcreatedate, assigneddatetime) -
Datediff(day, modifiedcreatedate, assigneddatetime) * 13 ) - (
Datediff(wk, modifiedcreatedate, assigneddatetime) * 2 - CASE
WHEN
Datepart(dw, modifiedcreatedate) = 1 THEN 1
ELSE 0
END + CASE
WHEN Datepart(dw, assigneddatetime) = 1 THEN 1
ELSE 0
END ) * 11
AS
WorkHourDiff,
Datediff(day, modifiedcreatedate, assigneddatetime) - (
Datediff(wk, modifiedcreatedate, assigneddatetime) * 2 - CASE
WHEN Datepart(dw,
modifiedcreatedate) = 1 THEN 1
ELSE 0
END + CASE
WHEN
Datepart(
dw,
assigneddatetime)
= 1 THEN 1
ELSE 0
END )
AS
workdaydiff
FROM (SELECT ticketid,
( CASE
WHEN Datepart(hour, origindatetime) > '17' THEN
Dateadd(hour, 6,
Cast(
Cast(Datepart(year, (Dateadd(day, 1, origindatetime))) AS
VARCHAR(4)
)
+ '-'
+ Cast(Datepart(month, (Dateadd(day, 1,
origindatetime))
)
AS VARCHAR
(4))
+ '-'
+ Cast(Datepart(day, (Dateadd(day, 1,
origindatetime)))
AS
VARCHAR(4)) AS
DATETIME))
WHEN Datepart(hour, origindatetime) < '6' THEN
Dateadd(hour, 6,
Cast(
Cast(Datepart(year, origindatetime) AS VARCHAR(4))
+ '-'
+ Cast(Datepart(month, origindatetime) AS
VARCHAR(4))
+ '-'
+ Cast(Datepart(day, origindatetime) AS
VARCHAR(4))
AS
DATETIME))
WHEN Datepart(dw, origindatetime) = '7' THEN
Dateadd(hour, 6, Cast(
Cast(Datepart(year, (Dateadd(day, 2, origindatetime))) AS
VARCHAR(4))
+ '-'
+ Cast(Datepart(month, (Dateadd(day, 2, origindatetime)))
AS VARCHAR(4))
+ '-'
+ Cast(Datepart(day, (Dateadd(day, 2, origindatetime))) AS
VARCHAR(4)) AS
DATETIME))
WHEN Datepart(dw, origindatetime) = '1' THEN
Dateadd(hour, 6, Cast(
Cast(Datepart(year, (Dateadd(day, 1, origindatetime))) AS
VARCHAR(4))
+ '-'
+ Cast(Datepart(month, (Dateadd(day, 1, origindatetime)))
AS VARCHAR(4))
+ '-'
+ Cast(Datepart(day, (Dateadd(day, 1, origindatetime))) AS
VARCHAR(4)) AS
DATETIME))
ELSE origindatetime
END ) AS modifiedCreateDate,
assigneddatetime
FROM dbo.it_track
WHERE ( Datediff(hour, origindatetime, assigneddatetime) IS NOT NULL ))
AS x
推荐答案
我建议您将此查询移至存储过程。在代码中使用此存储过程而不是使用查询。在调试代码时也尝试使用sql profiler来跟踪查询执行
I would suggest you to move this query to Stored Procedure. Use this Stored Procedure in your code instead of using the query. Also try to use sql profiler while debugging your code to trace the query execution
请试试下面的查询。我能想到的唯一原因是DATEPART中的变量可能不是有效日期。
Could you try the below query please. The only reason I could think of is the variable inside the DATEPART may not be a valid DATE.
select
AVG(weekdiff) as 'Response Time'
from
(select
TicketID,
modifiedCreateDate,
AssignedDateTime,
DATEDIFF(hour,
modifiedcreatedate,
AssignedDateTime) as actualhours,
DATEDIFF(day,
modifiedcreatedate,
AssignedDateTime)actualdays,
DATEDIFF(hour,
modifiedcreatedate,
AssignedDateTime)-( DATEDIFF(day,
modifiedcreatedate,
AssignedDateTime)*13)- ((datediff(wk,
modifiedCreateDate,
AssignedDateTime) * 2) - case
when datepart(dw,
CAST(modifiedCreateDate AS DATETIME)) = 1 then 1
else 0
end + case
when datepart(dw,
CAST(AssignedDateTime AS DATETIME)) = 1 then 1
else 0
end)*11 as Weekdiff
FROM
(SELECT
[TicketID],
(CASE
WHEN DATEPART(hour,
CAST(OriginDateTime AS DATETIME)) > '17' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
(DATEADD (day,
1,
CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(month,
(DATEADD (day,
1,
CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(day,
(DATEADD (day,
1,
CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) AS datetime))
WHEN DATEPART(hour,
CAST(OriginDateTime AS DATETIME)) < '6' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
CAST(OriginDateTime AS DATETIME)) AS varchar(4)) + '-' + CAST(DATEPART(month,
CAST(OriginDateTime AS DATETIME)) AS varchar(4)) + '-' + CAST(DATEPART(day,
CAST(OriginDateTime AS DATETIME)) AS varchar(4)) AS datetime))
WHEN DATEPART(dw,
CAST(OriginDateTime AS DATETIME)) = '7' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
(DATEADD (day,
2,
CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(month,
(DATEADD (day,
2,
CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(day,
(DATEADD (day,
2,
CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) AS datetime))
WHEN DATEPART(dw,
CAST(OriginDateTime AS DATETIME)) = '1' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
(DATEADD (day,
1,
CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(month,
(DATEADD (day,
1,
CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(day,
(DATEADD (day,
1,
CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) AS datetime))
ELSE CAST(OriginDateTime AS DATETIME)
end) AS modifiedCreateDate,
[AssignedDateTime]
FROM
[LFM_Archive].[dbo].[IT_Track]
WHERE
DATEDIFF(HOUR,[CAST(OriginDateTime AS DATETIME)],[AssignedDateTime]) IS NOT NULL)x
where
DATEPART(WEEK, CAST(modifiedCreateDate AS DATETIME)) = DATEPART(WEEK, getdate())
)y
引用：
CAST（CAST（DATEPART）（年，
（DATEADD（白天，
1，
OriginDateTime）））AS varchar（4））
CAST (CAST(DATEPART(year,
(DATEADD (day,
1,
OriginDateTime))) AS varchar(4))
两者之间日期时间格式的差异怎么样？情况呢？您当前的查询完全取决于特定的日期时间格式，它完全不兼容不同的区域设置。
我认为可以为连接设置标准语言（不知道如何影响那些设置）。但更好的解决方案是使查询独立于日期时间格式。
What about a difference in the date time format between the two situations? Your current query totally depends on a specific date time format, it is not at all compatible for different regional settings.
I think that it is possible to set the standard language for a connection (not sure how it affects those settings). But a better solution would be to make the query independent from the date time format.
这篇关于SQL Query有效，但它没有？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
