MSSQL DateTime2 Fail to Update

Question

Im running stored procedure which in charges to insert, update and delete table's entries. While both insert and delete runs smoothly, the update operation updates all columns except DATETIME2 one.

The scenario - I test my Repository pattern (using C# code) in the following way:

1. delete the entire [BackgroundTaskAttachtment] table
2. Create 4 new entries
3. delete single entry created on step 2
4. Wait for 5 seconds
5. modify one of the entries

the result is having 3 entries in [BackgroundTaskAttachtment] table, with all properties set as expected, except the [UpdatedOnUtc] which not updated (it is equal to [CreatedOnUtc]

I marked the updated row (as you can see [FilePath] was successfully updated): Would appreciate community insights,

Thank you

This is the stored procedure code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_ArrangeBackgroundTaskAttachtments]
(
    @backgroundTaskId   BIGINT,
    @taskAttchs         [dbo].[BackgroundTaskAttachtmentType] READONLY
)

AS

BEGIN
    SET NOCOUNT ON;
    --delete all removed attachtments
    DELETE FROM [BackgroundTaskAttachtment] 
    WHERE [BackgroundTaskId] = @backgroundTaskId AND [Id] NOT IN (SELECT [Id] FROM @taskAttchs)


----Update exist key-value pairs
UPDATE [dbo].[BackgroundTaskAttachtment]
SET 
    [IsPrimary] = attachs.[IsPrimary], 
    [FilePath] = attachs.[FilePath],
    [Bytes] = attachs.[Bytes],
    [UpdatedOnUtc] = GETUTCDATE()
FROM @taskAttchs AS attachs
WHERE attachs.[Id] = [BackgroundTaskAttachtment].[Id]

--insert new records
SELECT @backgroundTaskId AS [BackgroundTaskId], [FilePath], [IsPrimary], [Bytes], GETUTCDATE() AS [CreatedOnUtc], GETUTCDATE() AS [UpdatedOnUtc]
INTO #Temp FROM @taskAttchs as atcs
WHERE atcs.[Id] NOT IN (SELECT [Id] FROM [BackgroundTaskAttachtment] AS bta WHERE bta.[BackgroundTaskId] = @backgroundTaskId )

    INSERT INTO [BackgroundTaskAttachtment]([BackgroundTaskId], [IsPrimary], [Bytes], [FilePath], [CreatedOnUtc], [UpdatedOnUtc] )
    SELECT [BackgroundTaskId], [IsPrimary], [Bytes], [FilePath], [CreatedOnUtc], [UpdatedOnUtc]
    FROM #Temp
END

This is the table type (sent from CLR to SQL)

CREATE TYPE [dbo].[BackgroundTaskAttachtmentType] AS TABLE(

    [Id]                    [BIGINT]            NOT NULL,
    [FilePath]              [NVARCHAR](MAX)     NULL,
    [IsPrimary]             [BIT]               NOT NULL,
    [BackgroundTaskId]      [BIGINT]            NULL,
    [Bytes]                 [VARBINARY](MAX)    NULL
    )
GO

this is the table definition

CREATE TABLE [dbo].[BackgroundTaskAttachtment]
(
    [Id]                    BIGINT          IDENTITY(1,1)           NOT NULL,
    [BackgroundTaskId]      BIGINT                                  NOT NULL,
    [IsPrimary]             BIT                                     NOT NULL        DEFAULT 0,
    [FilePath]              NVARCHAR(MAX)                           NULL,
    [Bytes]                 VARBINARY(MAX)                          NULL,
    [CreatedOnUtc]          DATETIME2                               NOT NULL,
    [UpdatedOnUtc]          DATETIME2                               NOT NULL,
    [RowVersion]            ROWVERSION                              NOT NULL,

    CONSTRAINT [PK_dbo.BackgroundTaskAttachtment] PRIMARY KEY   CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.BackgroundTaskAttachtment_BackgroundTask_Id]
        FOREIGN KEY ([BackgroundTaskId])
        REFERENCES [dbo].[BackgroundTask] ([Id])
        ON DELETE CASCADE
);

Answer 1

Please try using SYSUTCDATETIME which returns datetime2. The GETUTCDATE which you are using, returns datetime.