CODEWITHSUNDEEP
sql-servertriggersconcatenationinsert-update
Silverwulf
Silverwulf

Reputation: 109

SQL Server INSERT/UPDATE Trigger concatenate date string

Never written a Trigger before.

Need to have a trigger on Insert and Update that checks a date, and if the day is greater than the 25th, sets that date back to the 25th of that month.

Right now I'm trying to build out the @newDate and I'm not sure how to do that.

Current Code:

CREATE TRIGGER [dbo].[setASDate]
   ON  [dbo].[AutoShip]
   AFTER UPDATE, INSERT
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @DistID INT;
DECLARE @date INT;
DECLARE @month INT;
DECLARE @year INT;  

IF UPDATE(DateNextShipment)
BEGIN
    DECLARE @newDate datetime;

    SELECT TOP 1 @DistID = DistID FROM INSERTED
    SELECT TOP 1 @date = DATEPART(d, DateNextShipment) FROM INSERTED
    SELECT TOP 1 @month = CONVERT(varchar(2),DATEPART(mm, DateNextShipment)) FROM INSERTED
    SELECT TOP 1 @year = CONVERT(varchar(4),DATEPART(yyyy, DateNextShipment)) FROM INSERTED
    @newDate = CONVERT(datetime,@year + '-' + @month + '-25')

    If @date > 25
    BEGIN       
        UPDATE AutoShip 
        SET DateNextShipment = @newDate, Day = 25 
        WHERE DistID = @DistID
    END     
END
END

EDIT - Updated the Code

Upvotes: 0

Views: 2492

Answers (1)

Sonam
Sonam

Reputation: 3466

Declare variables @date and @month of type varchar and then use it as:

SELECT TOP 1 @date = DATEPART(d, DateNextShipment) FROM INSERTED
SELECT TOP 1 @year= convert(varchar(4),DATEPART(yyyy, DateNextShipment)) FROM INSERTED
SELECT TOP 1 @month = convert(varchar(2),DATEPART(mm, DateNextShipment)) FROM INSERTED
@newDate = convert(datetime,@year+'-'+@month+'-'+'25')

Upvotes: 1

Related Questions