Click here to Skip to main content
14,970,849 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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


SQL
/****** 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


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
Posted
Updated 6-Jan-17 8:15am

1 solution

To reduce the result by 1 month simply use:

SQL
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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900