SQL Server error - Adding a value to a 'datetime' column caused an overflow

Question

I'm using SQL Server 2014. The last line causes causes an overflow error.

Why?

  DECLARE @CurrentDateTime  datetime
  DECLARE @PreviousYearDateTime  datetime
  DECLARE @ModifiedStartDateTime  datetime
  DECLARE @YearsYear   int = 2015

  SET @CurrentDateTime = '2016-08-29 19:13:30.840'      
  SET @PreviousYearDateTime = CONVERT(datetime, DATEADD(YEAR, -1, @CurrentDateTime))

  -- For the "startdate" - just use the previous "startdate".
  SET @ModifiedStartDateTime = @PreviousYearDateTime

  -- Set the 'year' to the year value.
  SET @ModifiedStartDateTime = DATEADD(YEAR, @YearsYear, DATEADD(YEAR, -DATEPART(YEAR, @ModifiedStartDateTime), @ModifiedStartDateTime))

Some simple debugging should reveal the issue. You are trying to set the year to 0 which is not valid. What are you trying to do here? — Sean Lange, Sep 16 '16 at 20:50
Can you explain what it is that you are trying to achieve with the code? There may be an easier way — Mike, Sep 16 '16 at 20:51

score 0 · Accepted Answer · answered Sep 16 '16 at 21:01

You can just replace the year like so:

SET @ModifiedStartDateTime = REPLACE(@ModifiedStartDateTime,DATEPART(YEAR,@ModifiedStartDateTime),@YearsYear)

But since you set the @ModifiedStartDateTime = @PreviousYearDateTime which has the year 2015... and you are changing the year to 2015... you aren't doing anything. Try using something different than 2015 for you @YearsYear

score -1 · Answer 2 · edited May 23 '17 at 12:19

-1

SQL Datetime

Date range January 1, 1753, through December 31, 9999

When you substract 2015 you are on year 0

There is DateTime2

range: 0001-01-01 through 9999-12-31.
January 1,1 CE through December 31, 9999 CE

how to solve 1753 year issue in sqlserver?

What is the significance of 1/1/1753 in SQL Server?

edited May 23 '17 at 12:19

Community

1
1

answered Sep 16 '16 at 20:52

Juan Carlos Oropeza

47,252
12
78
118

When i execute "select dateadd(year, 0, 0)" , it gives me "1900-01-01 00:00:00.000". why not "1753-01-01" ? – DatabaseCoder Sep 16 '16 at 21:05
I guess was [**sql design**](https://msdn.microsoft.com/en-us/library/ms186313.aspx) `The argument for date is the number 0. SQL Server interprets 0 as January 1, 1900.` – Juan Carlos Oropeza Sep 16 '16 at 21:08
Thank you!! Above two links (great great great grandfather) are very useful. – DatabaseCoder Sep 16 '16 at 21:37

SQL Server error - Adding a value to a 'datetime' column caused an overflow

2 Answers2