更改SQL表中的成员资格到期代码 [英] Change membership expiry code in SQL table

问题描述

嗨



我在SQL中有一个编程表，我需要修改它将使用它所要求的当前日期，然后在一年之后到期。目前我的代码使用规则在每个月的15日之前使用日期格式，当我们知道我们的系统时，它要么给予成员号码13个月而不是12个到期。我需要更改代码belwo和I我是SQL的新手，请你协助请

Hi

I have a programmed table in SQL that I need to modify that It will use the current date it is requested and then expire exactly a year later. Currently my code is using a rule off usign a date format before the 15th of each month and when we checke don our system it is either giving the membeship number a expiry of 13 months and not 12. I need to change the code belwo and I am a newbie at SQL, coudl you assist please

/****** Object:  StoredProcedure [dbo].[CP_Create_Card]    Script Date: 2016-11-29 08:41:26 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<author,,name>
-- Create date: <create date,,="">
-- Description:	<description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CP_Create_Card] 
	-- Add the parameters for the stored procedure here
 @membershipNumber VARCHAR(MAX),
 @CardId BIGINT = NULL OUTPUT,
 @minsToFirstDeadline int = 2880,--defailt 2 days
 @minsToSecondDeadline int = 5760 --default 4 days

	
AS
BEGIN
	DECLARE @CreatedDate DATETIME = GETDATE()
	DECLARE @ExpiryDate DATETIME
	DECLARE @FirstDeadine DATETIME
	DECLARE @SecondDeadline DATETIME
	IF DAY(@CreatedDate) < 15
	BEGIN
		SELECT @ExpiryDate = DATEADD(YEAR, 1, DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @CreatedDate) + 1, 0)))
	END
	ELSE
	BEGIN
		SELECT @ExpiryDate = DATEADD(YEAR, 1, DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @CreatedDate) + 2, 0)))
	END


	SELECT @FirstDeadine = DATEADD(MINUTE,@minsToFirstDeadline,GETDATE())
	SELECT @SecondDeadline = DATEADD(MINUTE,@minsToSecondDeadline,GETDATE())
	--Create Card Linked to Membership below
	IF EXISTS(SELECT * FROM Cards WHERE MembershipNumber = @membershipNumber AND Enabled = 1)
	BEGIN
		UPDATE Cards SET Enabled = 0 WHERE MembershipNumber = @membershipNumber AND Enabled = 1
	END

	INSERT INTO Cards (CardStatusId, MembershipNumber, PrintedDate, Created, Modified, ExpiryDate,FirstDeadline,SecondDeadline, Enabled) 
	VALUES(1, @membershipNumber, @CreatedDate, @CreatedDate, @CreatedDate, @ExpiryDate,@FirstDeadine,@SecondDeadline, 1)	
	SET @CardId = SCOPE_IDENTITY()

			-- Return Card Id

	
END

我的尝试：



不知道如何更改部分



IF DAY（@CreatedDate）< 15

BEGIN

SELECT @ExpiryDate = DATEADD（YEAR，1，DATEADD（d，-1，DATEADD（m，DATEDIFF（m，0，@ RegisteredDate）+ 1 ，0）））

结束

ELSE

BEGIN

SELECT @ExpiryDate = DATEADD（YEAR，1， DATEADD（d，-1，DATEADD（m，DATEDIFF（m，0，@ ManagingDate）+ 2,0）））

END

What I have tried:

Not sure how to change the section

IF DAY(@CreatedDate) < 15
BEGIN
SELECT @ExpiryDate = DATEADD(YEAR, 1, DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @CreatedDate) + 1, 0)))
END
ELSE
BEGIN
SELECT @ExpiryDate = DATEADD(YEAR, 1, DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @CreatedDate) + 2, 0)))
END

推荐答案

要将结果减少1个月，只需使用：

To reduce the result by 1 month simply use:

IF DAY(@CreatedDate) < 15
BEGIN
SELECT @ExpiryDate = DATEADD(YEAR, 1, DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @CreatedDate), 0)))
END
ELSE
BEGIN
SELECT @ExpiryDate = DATEADD(YEAR, 1, DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @CreatedDate) + 1, 0)))
END

这篇关于更改SQL表中的成员资格到期代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！