工作中遇到一个问题,A表中字段(DateTime1)的数据类型为DateTime,新建了一张表B的SMALLDATETIME1字段的数据来自A表的DateTime1
通过以下两篇文章知道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"
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
--如果存储过程存在,则删除重建
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
GO
1900-01-01之前的日期无法从DateTime转换成smalldatetime, smalldatetime时间范围"1900-01-01 00:00:00"到"2079-06-06 23:59:00"
DECLARE @date DATETIME
SET @date='1753-01-01 00:00:00.000'
SELECT CAST (@date AS SMALLDATETIME);