Divide by zero error encountered - SQL Server

Question

I'm generating a random DateTime in SQL Server table using the following sample code

Sample Code:

DECLARE @SeedInt INT = 0;
SELECT DATEADD(minute,(-1 * (ABS(Checksum(NewID()) % (CASE WHEN @SeedInt IS NULL OR @SeedInt <= 0 THEN 2 ELSE @SeedInt END - 1))) + 1), SYSUTCDATETIME());

Its working fine. But I tried the same approach in an UPDATE Query, its failed to Update and throws an exception

Table schema: StudentMark:

CREATE TABLE [dbo].[StudentMark]
(
    [StudentMarkId] [int] IDENTITY(1,1) NOT NULL,
    [StudentId] [uniqueidentifier] NOT NULL,
    [SubjectId] [uniqueidentifier] NOT NULL,
    [Score] [int] NOT NULL,
    [ScoreInfo] [xml] NOT NULL,
    [GeneratedOn] [datetime2](2) NOT NULL,
    [IsPass] [bit] NOT NULL,
    CONSTRAINT [PK_StudentMark] 
       PRIMARY KEY CLUSTERED ([StudentMarkId] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Sample seed data

INSERT INTO [dbo].[StudentMark] ([StudentId], [SubjectId], [ScoreInfo], [GeneratedOn], [Score], [IsPass])
VALUES ('FC3CB475-B480-4129-9190-6DE880E2D581', '0D72F79E-FB48-4D3E-9906-B78A9D105081', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-10 10:10:15', 95, 1),
       ('0F4EF48C-93E3-41AA-8295-F6B0E8D8C3A2', '0D72F79E-FB48-4D3E-9906-B78A9D105081', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-10 10:10:15', 100, 1),
       ('0F4EF48C-93E3-41AA-8295-F6B0E8D8C3A2', 'AB172272-D2E9-49E1-8040-6117BB6743DB', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-16 09:06:20', 25, 0),
       ('FC3CB475-B480-4129-9190-6DE880E2D581', 'AB172272-D2E9-49E1-8040-6117BB6743DB', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-16 09:06:20', 82, 1);

Update Query: (SQL Server)

UPDATE SInfo
    SET
        GeneratedOn = DATEADD(minute,(-1 * (ABS(Checksum(NewID()) % (CASE WHEN SInfo.StudentMarkId IS NULL OR SInfo.StudentMarkId <= 0 THEN 2 ELSE SInfo.StudentMarkId END - 1))) + 1), SYSUTCDATETIME())
    FROM dbo.StudentMark AS SInfo

But the query throws an exception

Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. The statement has been terminated.

Kindly assist me whats wrong in the Query, what needs to do to fix this issue?

If StudentMarkId is one, you'll get zero when you subtract one. To fix, don't divide by zero. I can't begin to understand what that calculation is trying to accomplish, so I don't know exactly what would be needed — UnhandledExcepSean, Aug 22 '17 at 18:53
@Ghost - In my Sample code, I generated the `DateTime` using `DECLARE @SeedInt INT = 0;` Show how it will case an issue? — B.Balamanigandan, Aug 22 '17 at 18:55

Gordon Linoff · Answer 1 · 2017-08-23T02:40:55.067

1

This happens when the % operand is 0. So, use NULLIF():

UPDATE SInfo
    SET GeneratedOn = DATEADD(minute,
                              (-1 * (ABS(Checksum(NewID()) % NULLIF(CASE WHEN SInfo.StudentMarkId IS NULL OR SInfo.StudentMarkId <= 0 THEN 2 ELSE SInfo.StudentMarkId END - 1), 0)) + 1)), SYSUTCDATETIME())
    FROM dbo.StudentMark SInfo;

edited Aug 23 '17 at 02:40

answered Aug 22 '17 at 19:03

Gordon Linoff

1,242,037
58
646
786

1

Kindly check the query, it has a Syntax issue. – B.Balamanigandan Aug 22 '17 at 19:07

Divide by zero error encountered - SQL Server

1 Answers1