T-Sql dateadd() How to fix overflow error

Question

I'm using the dateadd function in tsql and I'm getting an overflow error. Here is my code:

insert into dbo.PayPeriodLookups (PayPeriodEndDate, PayPeriodStartDate)
values
(20161015, dateadd(day, -13, '2016/10/15')),
(20161029, dateadd(day, -13, '2016/10/29')),
(20161112, dateadd(DAY, -13, '2016/11/12')),
(20161126, dateadd(DAY, -13, '2016/11/26')),
(20161210, dateadd(DAY, -13, '2016/12/10')),
(20161224, dateadd(Day, -13, '2016/12/24')),
(20170107, dateadd(day, -13, '2017/01/07')),
(20170121, dateadd(day, -13, '2017/01/21'))

I keep getting this error when I try to execute this script: 'Arithmetic overflow error converting expression to data type datetime. The statement has been terminated.'

Anyone know as to why I'm getting this error? I thought this was the way that the dateadd function worked...

Tip: Including the table schema with the column data types in the question is always a good idea. — HABO
– HABO, Commented Dec 23, 2016 at 17:34

Siyual · Accepted Answer · 2016-12-23 16:15:23Z

2

This isn't an issue with DATEADD(), this is an issue with the PayPeriodEndDate values you're inserting. You're using INT values to represent the dates, when you should be wrapping them in single quotes.

You're trying to do an implicit cast from an INT to a DATETIME, which cannot be done the way you're doing it. The correct way to represent dates is via 'yyyy-mm-dd'. Use the following instead:

Values
(Cast('2016-10-15' As DateTime), DateAdd(Day, -13, '2016-10-15')),
(Cast('2016-10-29' As DateTime), DateAdd(Day, -13, '2016-10-29')),
(Cast('2016-11-12' As DateTime), DateAdd(Day, -13, '2016-11-12')),
(Cast('2016-11-26' As DateTime), DateAdd(Day, -13, '2016-11-26')),
(Cast('2016-12-10' As DateTime), DateAdd(Day, -13, '2016-12-10')),
(Cast('2016-12-24' As DateTime), DateAdd(Day, -13, '2016-12-24')),
(Cast('2017-01-07' As DateTime), DateAdd(Day, -13, '2017-01-07')),
(Cast('2017-01-21' As DateTime), DateAdd(Day, -13, '2017-01-21'))

edited Dec 23, 2016 at 16:15

answered Dec 23, 2016 at 16:10

Siyual

17k8 gold badges47 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Cappelletti Over a year ago

Just more readable (which I agree with), however you can Select cast('20161015' as date)

Vamsi Prabhala Over a year ago

@Siyual--may be use cast(.. as datetime) to be more correct.

Siyual Over a year ago

@vkp There shouldn't really be a need to CAST it, since the column is already a DATETIME datatype (as per the error).

John Cappelletti · Accepted Answer · 2016-12-23 17:09:41Z

Either quote your int values as illustrated below

Declare @T1 table (PayPeriodEndDate datetime,PayPeriodStartDate datetime)
Insert Into @T1 values
('20161015', dateadd(day, -13, '2016/10/15')),
('20161029', dateadd(day, -13, '2016/10/29')),
('20161112', dateadd(DAY, -13, '2016/11/12')),
('20161126', dateadd(DAY, -13, '2016/11/26')),
('20161210', dateadd(DAY, -13, '2016/12/10')),
('20161224', dateadd(Day, -13, '2016/12/24')),
('20170107', dateadd(day, -13, '2017/01/07')),
('20170121', dateadd(day, -13, '2017/01/21'))

Select * from @T1

Or cast your int values as varchar(8)

Declare @T2 table (PayPeriodEndDate datetime,PayPeriodStartDate datetime)
Insert Into @T2 values
(cast(20161015 as varchar(8)), dateadd(day, -13, '2016/10/15')),
(cast(20161029 as varchar(8)), dateadd(day, -13, '2016/10/29')),
(cast(20161112 as varchar(8)), dateadd(DAY, -13, '2016/11/12')),
(cast(20161126 as varchar(8)), dateadd(DAY, -13, '2016/11/26')),
(cast(20161210 as varchar(8)), dateadd(DAY, -13, '2016/12/10')),
(cast(20161224 as varchar(8)), dateadd(Day, -13, '2016/12/24')),
(cast(20170107 as varchar(8)), dateadd(day, -13, '2017/01/07')),
(cast(20170121 as varchar(8)), dateadd(day, -13, '2017/01/21'))

Select * from @T2

Collectives™ on Stack Overflow

T-Sql dateadd() How to fix overflow error

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related