大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。
Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺
工作中遇到一个问题,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
View Code
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);
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/164636.html原文链接：https://javaforall.net
