Transact SQL – DateAdd and avoid overflow errors

Background

Transact SQL has adequate support for Date arithmetic exposed through dateadd, datediff, etc.

Interesting yesterday I ran into a stumbling block due to a relatively high value for the interval argument.

It graciously handles ints intervals.

But, choked on a bigint.

Error

Error Text

Msg 8115, Level 16, State 2, Line 14
Arithmetic overflow error converting expression to data type int.

Error Image

Workaround

Overview

  1. The workaround proposed by Michael Valentine Jones goes like this
    • Rather than try to add the interval at one time, add succesively
      • Add Days
        • Divide the original increment by 86400 if adding seconds
          • 86400
            • 24 Hours * 60 minutes * 60 seconds
      • Add Seconds
        • Get what remains
          • Get module of the 86400
        • Apply what remained

Code

[dbo].[udfn_dateAddMilliSecondAvoidOverflow]


use master
go

if object_id('dbo.udfn_dateAddMilliSecondAvoidOverflow') is null
begin

	exec
	(
		'
			create function dbo.udfn_dateAddMilliSecondAvoidOverflow()
			returns datetime
			as
			begin
				return 0
			end

		'
	)
end
go

alter function [dbo].[udfn_dateAddMilliSecondAvoidOverflow]
(
	  @increment bigint
	, @base		 datetime

)
returns datetime
as
begin

	return
		(
			dateadd
			(
				  MILLISECOND
				, @increment % (86400 * 1000 )
				, dateadd
					(
						  day
						, @increment / ( 86400 * 1000)
						, @base
					)


			)
		)

end

go

grant execute on [dbo].[udfn_dateAddMilliSecondAvoidOverflow] to [public]
go
	

Sample Data set

 

Credit Roll

Crediting Michael Valentine Jones
Link

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s