datediff更新第二张表的SQL问题. [英] SQL Problem with datediff updating second table.

问题描述

我在SQL Server 2008中创建了两个表.一个是"Customers"和"CustomerLastAction"
CustomerLastAction有两列:custID和LastActionDate.
客户的列名为状态"，以此类推.0 =客户处于活动状态，1 =客户未处于活动状态.....

如果CustomerLastAction中的LastActionDate超过1年，则将customers表中的状态更新为未激活

更改过程[dbo].[uspUpdateStatus]
AS
开始
设置NOCOUNT ON

@@ ROWCOUNT<> 0

开始

更新客户
SET Customer.custStatus = 1
WHERE Customer.custID =(
SELECT cla.custID
来自CustomerLastAction cla
在哪里(DATEDIFF(YEAR，cla.LastActionDate，GETDATE()))> 1
)
继续

END
END

这不起作用...我想念什么...

好吧...这行得通...花点时间..LOL

更改过程[dbo].[uspUpdateStatus]
AS
开始
设置NOCOUNT ON
-
声明@ID INT

DECLARE LastAction游标
FOR
SELECT cla.custID
来自CustomerLastAction cla
在哪里(DATEDIFF(DAY，cla.LastActionDate，GETDATE()))& gt; 365

OPEN LastAction

从LastAction获取下一个
INTO @Id

@@ FETCH_STATUS = 0
开始
更新客户
SET Customer.custStatus = 0
在哪里Customer.custID = @ID
从LastAction获取下一个
INTO @Id
END
CLOSE LastAction
取消分配LastAction
END

I to two tables in SQL Server 2008. One is "Customers" and "CustomerLastAction"
CustomerLastAction has two columns: custID and LastActionDate.
Customers have a columns call "Status", etc. 0=Customer is Active, 1=customer Is not Active.....

When LastActionDate in CustomerLastAction is older than 1 year, update status in customers table to not active

ALTER PROCEDURE [dbo].[uspUpdateStatus]
AS
BEGIN
SET NOCOUNT ON

WHILE @@ROWCOUNT <> 0

BEGIN

UPDATE Customers
SET Customers.custStatus = 1
WHERE Customers.custID = (
SELECT cla.custID
FROM CustomerLastAction cla
WHERE (DATEDIFF(YEAR, cla.LastActionDate, GETDATE())) > 1
)
CONTINUE

END
END

This is not working...what am i missing...

Okay...This works...Took awhile..LOL

ALTER PROCEDURE [dbo].[uspUpdateStatus]
AS
BEGIN
SET NOCOUNT ON
--
DECLARE @ID INT

DECLARE LastAction CURSOR
FOR
SELECT cla.custID
FROM CustomerLastAction cla
WHERE (DATEDIFF(DAY, cla.LastActionDate, GETDATE())) &gt; 365

OPEN LastAction

FETCH NEXT FROM LastAction
INTO @Id

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Customers
SET Customers.custStatus = 0
WHERE Customers.custID = @ID
FETCH NEXT FROM LastAction
INTO @Id
END
CLOSE LastAction
DEALLOCATE LastAction
END

推荐答案

DATEDIFF()函数在T-SQL中...有关详细信息，请访问:

DATEDIFF() function in T-SQL... read more at: http://msdn.microsoft.com/en-us/library/ms189794.aspx[^]

Try the following code with any date (@date1 variable) to understand returning values:

DECLARE @date1 as DateTime
DECLARE @date2 as DateTime

SET @date1 = '2010-08-05';
SET @date2 = GETDATE()

--return days diff...
SELECT CONVERT(INT,@date1 - @date2) AS RetVal

--return year diff...
SELECT DATEDIFF(yyyy,@date1 , @date2) AS RetVal

在不了解这不起作用"的真正含义的情况下.我个人将您的datadiff语句更改为以下内容

Without knowing more about what "This is not working" really means. Personally I would change your datadiff statement to the following

SELECT cla.custID
FROM CustomerLastAction cla
WHERE (DATEDIFF(day, cla.LastActionDate, GETDATE())) > 365

在datadiff上的 ASP.NET线程中还有许多其他解决方案 [

there are also many other solutions in the this ASP.NET thread on datadiff[^]

这篇关于datediff更新第二张表的SQL问题.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！