帮助SQL Query进行更新。 [英] Help in SQL Query for updating.
问题描述
我有以下
创建 表 TblSubscription
(
SubscriptionId int identity ( 1 , 1 )主要 < span class =code-keyword> key ,
UserId INT NOT NULL 外来 密钥 引用 TblAppUser(UserId),
PackageId INT NOT NULL 外来 密钥 引用 TblPackages(PackageID),
StartDate datetime ,
FinishDate datetime ,
mxnotification int ,
mxarticles int ,
mxvideos int ,
mxevent int ,
mxallias int ,
IsActive 位,
IsPaid 位</ span>
);
INSERT INTO TblSubscription(UserId,PackageId,StartDate ,FinishDate,mxnotification,mxarticles,mxvideos,mxevent,mxallias,IsActive,IsPaid)
VALUES ( @ UserID , @ PackageId ,GetDate(),GETDATE()+ 30, @ notific , @ articles , @ mxvideos , @ mxevent , @ mxallias , 0 , 0 ) SELECT @@ IDENTITY
我想要列名称startdate = GEtdate()和finshdate = getdate()+ 30 equals 。它为该Userid自动设置IsActive = false
和ifmxnotification,mxarticles,mxvideos,mxevent,mxallias = 0它也会自动为该Userid设置IsActive = false
我该怎么做?是否有任何函数或其他可用的东西。
在你的SQL查询中'30'引用了什么?是月,年,毫秒等?我相信你所追求的是DATEADD(datepart,number, date )// number 一个整数函数。如果您将月份添加到当前日期(GETDATE()),那么您的查询部分将如下所示
... 值(...,DATEADD(日, 30 ,GETDATE(),...)现在,如果你只是想从当前日期开始30天,以下内容相当于上述内容:
... 值(...,DATEADD(月, 1 ,GETDATE(),...
以下是更多信息,让您熟悉SQL Server中的DATE
函数。
http://technet.microsoft.com/ en-us / library / ms186819(v = sql.100).aspx [ ^ ]
希望有所帮助...
SS
I have following
Create Table TblSubscription
(
SubscriptionId int identity(1,1) Primary key,
UserId INT NOT NULL Foreign key References TblAppUser(UserId),
PackageId INT NOT NULL Foreign key References TblPackages(PackageID),
StartDate datetime,
FinishDate datetime,
mxnotification int,
mxarticles int,
mxvideos int,
mxevent int,
mxallias int,
IsActive bit,
IsPaid bit
);
INSERT INTO TblSubscription (UserId,PackageId,StartDate,FinishDate,mxnotification,mxarticles,mxvideos,mxevent,mxallias,IsActive,IsPaid)
VALUES(@UserID,@PackageId,GetDate(),GETDATE()+30,@notific,@articles,@mxvideos,@mxevent,@mxallias,0,0) SELECT @@IDENTITY
I want that column name startdate= GEtdate() and finshdate=getdate()+30 equals. it automaically sets IsActive = false for that Userid
And Ifmxnotification,mxarticles,mxvideos,mxevent,mxallias =0 it also automatically sets IsActive =false for that Userid
How can i do that. is there any function or other stuff available.
What does the '30' refer to in your sql query? Is it month, year, millisecond, etc? I believe what you're after is theDATEADD(datepart, number, date) //number is an Integerfunction. If you're adding month to the current date (GETDATE()) then your portion of the query will look like this
...values(..., DATEADD(day, 30, GETDATE(),...)Now, if you're just trying to get 30 days out from your current date and the following is equivalent to the above:
...values(..., DATEADD(month, 1, GETDATE(),...
Here is more information to get yourself familiar with theDATE
function in SQL Server.
http://technet.microsoft.com/en-us/library/ms186819(v=sql.100).aspx[^]
Hope that helps...
SS
这篇关于帮助SQL Query进行更新。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!