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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CP_Create_Card]
@membershipNumber VARCHAR(MAX),
@CardId BIGINT = NULL OUTPUT,
@minsToFirstDeadline int = 2880,
@minsToSecondDeadline int = 5760
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())
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()
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