How to calculate age by day, month and year in in trigger update SQL-CodePudding

I am using this code but something is wrong.

I tried to use many codes but failed to achieve the equation.

I enter to calculate the age add day in day and month in month and year in year

ALTER TRIGGER [dbo].[trg_DeasignTeamMonthly]
ON [dbo].[StudentData]
AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
    UPDATE dbo.StudentData 
    SET [Day] = DATEDIFF(DAY, StudentData.DateOfBirth,[dbo].StudentData.DateToDay) / 365

    UPDATE dbo.StudentData 
    SET [Month] = MONTH([dbo].StudentData.DateToDay) - 1

    UPDATE dbo.StudentData 
    SET [Year] = YEAR([dbo].StudentData.DateToDay) - YEAR([dbo].StudentData.DateOfBirth)
END

CodePudding user response：

I create a stored procedure for calculating for this purpose, you could using it in anywhere that you need (trigger, other stored procedure ,)

CREATE PROCEDURE CalcDurationRange
    @StartDT DATETIME,
    @EndDT DATETIME,
    @Year INT OUT,
    @Month INT OUT,
    @Day INT OUT
AS 
    DECLARE @xDate DATETIME = DATEADD(YEAR,YEAR(@EndDT)-YEAR(@StartDT),@StartDT)

    IF(@xDate>@EndDT)
        SET @xDate = DATEADD(YEAR,-1,@xDate)
    
    SET @Year = DATEDIFF(YEAR,@StartDT,@xDate)   
    SET @Month = DATEDIFF(MONTH,@xDate,@EndDT)
        
    IF(DATEADD(MONTH,@Month,@xDate)>@EndDT)
        SET @Month = @Month -1  
     
    SET @Day = DATEDIFF(DAY,DATEADD(MONTH,@Month,@xDate),@EndDT)
GO

sample of using this SP :

DECLARE @xYear INT, @xMonth INT, @xDay INT
EXEC CalcDurationRange 
    '1987-12-01',
    '2021-10-01',
    @xYear  OUT,
    @xMonth OUT,
    @xDay OUT
    
PRINT @xYear         
PRINT @xMonth
PRINT @xDay