Month increment query

Question

I want to update month in my database by adding 1 month but I don't know how I want to add month in my following stored procedure query I am not good in sql kindly check it

ALTER PROCEDURE [dbo].[ChangePassword] 

@password varchar(20),
@epassword varchar(50),
@username char(32)
AS
UPDATE AccountRole 
    SET Password = @password, 
        EPassword = @epassword 
    WHERE UserName = @username

    UPDATE AccountRole 
    SET ExpiryDate="?"

Hhere what do I have to write to increment 1 month when query executes

Answer 1

Try this, no needs to use second update:

ALTER PROCEDURE [dbo].[ChangePassword] 

@password varchar(20),
@epassword varchar(50),
@username char(32)
AS
UPDATE AccountRole 
    SET Password = @password, 
        EPassword = @epassword,
        ExpiryDate=DATEADD(MONTH, 1, GETDATE()) 
    WHERE UserName = @username

Answer 2

You can use dateadd.

update AccountRole set ExpiryDate=dateadd(month, 1, ExpiryDate)
where ...

Answer 3

To increment an existing value of type DATETIME or DATE by one month, use:

UPDATE AccountRole 
SET ExpiryDate = DATEADD(MONTH, 1, ExpiryDate)

and as Oleg rightfully pointed out, assuming that your two UPDATE statements have the same WHERE condition (WHERE UserName = @username), then you could do this in a single UPDATE:

UPDATE dbo.AccountRole 
SET Password = @password, 
    EPassword = @epassword,
    ExpiryDate = DATEADD(MONTH, 1, ExpiryDate)
WHERE UserName = @username