I currently have a warranty function (which is being used for an INSERT query) that determines whether a warranty is still valid. It compares warranty_remaining ( DATEDIFF between the date of purchase and today's date) with warranty_duration (3 years aka the warranty duration), if the warranty_remaining is less than or equal to the warranty duration then it should return the warranty_remaining as expected. Else, it will return a null indicating the warranty has expired.

However, there seems to be an error, as I get a NULL returned in the warranty row. After I run my INSERT query, every value is expected except for warranty. I suspect the issue lies in the way/format the warranty_remaining is being returned after the IF block. I say this because I've tried testing the if statement/calculations above the return statement ( RETURN @warranty_remaining; ) to check if its working as expected. (i.e., I changed the return statement to return 1000 instead of @ warranty_remaining . In the end, it did show 1000 in the warranty row. Which tells me it shouldn't be a logic / calculation error in the code).

Another possibility would be how the function is being called in the INSERT query but it seems pretty slim.

Notes:

• Whenever I wanted to test changes to the function, I deleted always deleted the existing instance in the Programmability/Functions/Scalar-valued Functions beforehand
• The datatype of warranty is int, hence it shouldn't be a datatype mismatch since the function has RETURNS INT
• PK is product_id and I set Is Identity to Yes
• Assume warranty is 3 years
• If the function truly can't work then I guess it has to be carried out in the individual VALUES

Code for function:

USE YourDatabaseName; -- Replace 'YourDatabaseName' with the actual name of your database

-- Start a new batch
GO

CREATE FUNCTION dbo.CalculateWarranty 
    (@purchase_date DATE)
RETURNS INT
AS
BEGIN
    DECLARE @warranty_duration INT;
    DECLARE @warranty_remaining INT;

    -- Set the warranty duration to 3 years (in days)
    SET @warranty_duration = 1095;

    -- Calculate the remaining warranty duration in days
    SET @warranty_remaining = DATEDIFF(DAY, @purchase_date, GETDATE());

    -- If the remaining warranty is within 3 years, return the remaining duration; otherwise, return NULL
    IF @warranty_remaining <= @warranty_duration
        RETURN @warranty_remaining;

    -- Default return statement (return NULL if warranty is not within 3 years)
    RETURN NULL;        
END;

Code for INSERT query:

INSERT INTO product(supplier_id, category_id, serial_no,
    product_brand, product_name, purchase_price,
    purchase_date, warranty, description) 
VALUES (1, 1, 'PF-2HKJ16',
        'Lenovo', 'ThinkPad X1 Carbon 8 / i7-10510U', NULL, -- Enter purchase_price later,
        '2021-11-01',
        dbo.CalculateWarranty(CONVERT(DATE, GETDATE())), -- Call function called CaclulateWarranty
        'The ThinkPad X1 Carbon 8th generation with an Intel Core i7-10510U processor is a premium ultrabook laptop developed by Lenovo. It is designed to deliver exceptional performance, portability, and durability for professionals and business users.');