CODEWITHSUNDEEP
sql-server
3313 aloha
3313 aloha

Reputation: 3

The datediff function resulted in an overflow. How to handle using Datatype

I am using bigint to handle to datediff function in milliseconds. How to handle Using datatype

I am using stored procedure

alter function [dbo].[date2timestamp] (@dateStr char(255)) returns bigint as

begin

  return convert(bigint, datediff(microsecond, '01-01-1970 00:00:00',@dateStr))

end

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Upvotes: 0

Views: 134

Answers (1)

Thom A
Thom A

Reputation: 95830

There's a couple of issues here. Firstly, your input parameter is wrong; it should be a date and time value (I'm going to assume a datetime2(7)).

Also if you need a bigint then use DATEDIFF_BIG, which returns a bigint; it's no good converting an int to a bigint if the int has already overflowed:

ALTER FUNCTION [dbo].[date2timestamp] (@Date datetime2(7))
RETURNS bigint
AS
BEGIN

    RETURN DATEDIFF_BIG(MICROSECOND, '19700101', @Date);

END;

If, however, you aren't using SQL Server 2019+ I don't recommend using a scalar function either. Switch to an inline table value function:

DROP FUNCTION IF EXISTS [dbo].[date2timestamp]; --As you can't ALTER function from scalar to table value
GO
ALTER FUNCTION [dbo].[date2timestamp] (@Date datetime2(7))
RETURNS table
AS
    RETURN SELECT DATEDIFF_BIG(MICROSECOND, '19700101', @Date) AS date2timestamp;

Upvotes: 0

Related Questions