|
|
Error Message
Server: Msg 298, Level 16, State 1, Line 1
The conversion from datetime data type to smalldatetime
data type resulted in a smalldatetime overflow error.
Causes:
As the message suggests, you are trying to convert a datetime data type into a smalldatetime data type and the date is either before January 1, 1900 or after June 6, 2079.
|
As a reference, a datetime data type can contain date and time data from January 1, 1753 through December 31, 1999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.003333 seconds). On the other hand, a smalldatetime data type can contain date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. Smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded to the nearest minute.
Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.
On the other hand, the smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.
Here’s an example on how the error can be encountered. Let’s assume you have a table that contains the birthdays of the U.S presidents, as follows:
CREATE TABLE [dbo].[US President] (
[Name] VARCHAR(50),
[Birthday] DATETIME
)
Name Birthday
-------------------- -----------
James Monroe 04/28/1758
Abraham Lincoln 02/12/1809
Woodrow Wilson 12/28/1856
William Taft 09/15/1857
Theodore Roosevelt 10/27/1858
The following SELECT statement will give you the error:
SELECT [Name], CAST([Birthday] AS SMALLDATETIME) AS [Birthday]
Server: Msg 298, Level 16, State 1, Line 1
The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime
overflow error.
Another way of encountering the error is when changing the data type of the column from a datetime into smalldatetime using the ALTER TABLE and there are values in the column that is either before January 1, 1900 or after June 6, 2079:
ALTER TABLE [dbo].[US President]
ALTER COLUMN [Birthday] SMALLDATETIME
Server: Msg 298, Level 16, State 1, Line 1
The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime
overflow error.
The statement has been terminated.
Solution / Work Around:
If your datetime data type column contains values before January 1, 1900 or after June 6, 2079, then you don’t have a choice but to leave it as datetime data type and will not be able to change it to a smalldatetime data type. To identity rows in your table that cannot be converted to smalldatetime, you can easily check the dates and see if it is beyond the smalldatetime ranges:
SELECT * FROM [US President]
WHERE [Birthday] < '01/01/1900' OR [Birthday] > '06/06/2079'
If you still want to convert the column into smalldatetime when returning from a SELECT statement and just return NULL if the value cannot be converted to smalldatetime, then you can do the following SELECT statement:
SELECT [Name], CASE WHEN [Birthday] < '01/01/1900' OR
[Birthday] > '06/06/2079' THEN NULL
ELSE [Birthday] END AS [Birthday]
FROM [dbo].[US President]
|