大家好，又见面了，我是你们的朋友全栈君。
工作中遇到一个问题,A表中字段(DateTime1)的数据类型为DateTime,新建了一张表B的SMALLDATETIME1字段的数据来自A表的DateTime1
但在将A表字段DateTime1导出到B表的 SMALLDATETIME1字段时出现了以下错误
后经过排查发现在原来是A表 DateTime1字段的值有许多是”1753-01-01 00:00:00.000″,从而导致转换失败
虽然知道了是什么原因导致的,但还是不太明白为什么”1753-01-01″无法转换成SMALLDATETIME类型
通过以下两篇文章知道DateTime与smalldatetime的差别(smalldatetime仅Sqlserver2005以上版本支持,2005不支持)
DateTime时间范围”1753-01-01 00:00:00.000″到”9999-12-31 23:59:59.997″
smalldatetime时间范围”1900-01-01 00:00:00″到”2079-06-06 23:59:00″
MSDN datetime and smalldatetime
datetime
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.
smalldatetime
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 up to the nearest minute.
Date and time types in SQL Server
As of SQL Server 2008, we have several new types related to date and time:
--如果存储过程存在,则删除重建 IF EXISTS(select1from sys.objects where type='p' AND name='HTL_Convent_DateTime') DROP PROCEDURE HTL_Convent_DateTime; --必须加上Go,否则下面创建存储过程时会出现错误"MSSQL 'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。" GO --对输入的日期进行各种日期格式转换 --HLT --'2014-07-30 15:12:17' CREATE PROCEDURE HTL_Convent_DateTime @date_time DATETIME AS BEGIN SELECT @date_time AS 'DateTime', CAST (@date_time AS DATETIME2) AS 'DateTime2' , CAST (@date_time AS DATE) AS 'DATE' , CAST (@date_time AS TIME) AS 'TIME' , CAST (@date_time AS datetimeoffset) AS 'datetimeoffset' SELECT CAST (@date_time AS SMALLDATETIME)AS 'SMALLDATETIME'; END GOView Code
1900-01-01之前的日期无法从DateTime转换成smalldatetime, smalldatetime时间范围”1900-01-01 00:00:00″到”2079-06-06 23:59:00″
2076-06-06以后的日期也无法转换
在 smalldatetime时间范围内的日期
DECLARE @date DATETIME SET @date='1753-01-01 00:00:00.000' SELECT CAST (@date AS SMALLDATETIME);
参考：
http://www.karaszi.com/SQLServer/info_datetime.asp#DtInSqlServer
http://technet.microsoft.com/en-us/library/aa258277(v=sql.80).aspx
http://www.cnblogs.com/dxmdiy/archive/2012/11/01/2749800.html
发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/164636.html原文链接：https://javaforall.cn
