CODEWITHSUNDEEP
sqlsql-server
WaterBoy
WaterBoy

Reputation: 709

SQL Server FUNCTION overflow int vs bigint

I am using a SQL Server function which returns a bigInt and using this in a trigger to assign a value to a column of type bigint. However when I run the trigger, an overflow exception occurs (Arithmetic overflow error converting expression to data type int.), i.e. it treats it as an int, not a bigint

The function is:

ALTER FUNCTION [dbo].[longIntDateTime] ()
RETURNS bigint
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ResultVar bigint;
    DECLARE @now Datetime;

    set @now = getdate();
    SET @ResultVar=DATEPART(YYYY,@now)*100000000 + DATEPART(MM,@now)*1000000 + DATEPART(DD,@now)*10000 + DATEPART(HH,@now)*100;
    --  DATEPART(HH,@now)*100 +  DATEPART(MI,@now);

    -- Return the result of the function
    RETURN (@ResultVar);
END

The trigger is:

ALTER TRIGGER [dbo].[employeesInsert] 
ON  [dbo].[employees] 
AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    UPDATE employees
    SET changeTimeStamp = dbo.longIntDateTime()
    FROM inserted INNER JOIN employees On inserted._id = employees._id  
END

and the table definition is:

CREATE TABLE [dbo].[employees](
    [_id] [int] IDENTITY(1,1) NOT NULL,
    [employee_name] [varchar](50) NOT NULL,
    [password] [varchar](50) NOT NULL,
    [isActive] [int] NOT NULL,
    [isDeleted] [int] NOT NULL,
    [changeTimeStamp] [bigint] NOT NULL,

    CONSTRAINT [PK_employees] PRIMARY KEY CLUSTERED ([_id] ASC)
)

ALTER TABLE [dbo].[employees] 
   ADD CONSTRAINT [DF_employees_isActive]  DEFAULT ((0)) FOR [isActive]

ALTER TABLE [dbo].[employees] 
   ADD CONSTRAINT [DF_employees_isDeleted]  DEFAULT ((0)) FOR [isDeleted]
GO

If I take two '0's off the first yyyy part of the function, the trigger succeeds, however as is, it fails.

Clearly the value produced is less than a big int.

any ideas?

anton

Upvotes: 1

Views: 988

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

The problem is this line of code:

SET @ResultVar=DATEPART(YYYY,@now)*100000000 + DATEPART(MM,@now)*1000000 + DATEPART(DD,@now)*10000 + DATEPART(HH,@now)*100;

The constants are interpreted as int so the entire calculate is done that way. You can fix this easily by casting the first to bigint:

SET @ResultVar=DATEPART(YYYY,@now)*cast(100000000 as bigint)+ DATEPART(MM,@now)*1000000 + DATEPART(DD,@now)*10000 + DATEPART(HH,@now)*100;

Upvotes: 1

x2.
x2.

Reputation: 9668

It's because DATEPART returns int.Try to cast to bigint before multiplying

cast (DATEPART(YYYY,@now) as bigint)*100000000

Upvotes: 0

Related Questions