⽇期处理datetime和date之间的相互转换
⽇期处理datetime和date之间的相互转换 (转载)
⽇期是数据处理中经常使⽤到的信息之⼀。⽣⽇、数据处理时间、计划的预计完成时间，按年、季、⽉的统计，这些都属于⽇期处理的范畴。由于⽇期中包含了年、季、⽉、⽇等众多信息，不同的国家对⽇期格式、⽇期⽂字描述及星期有不同的规定，因此产⽣了⽇期处理的复杂性。本章主要讨论在SQL Server数据库中对⽇期的各种处理⽅法。
⽇期类型概述
SQL Server中的⽇期类型包括datetime和smalldatetime，仅能处理可以识别为1753年～9999年间的⽇期的值，没有单独的⽇期型或时间型。
1．datetime
datetime类型处理从1753年1⽉1⽇～9999年12⽉31⽇的⽇期和时间数据，精确度为百分之三秒。即：对于0.000～0.001、0.009的⽇期值，调整为0.000；对于0.002～0.004的⽇期值，调整为0.003；对
于0.005～0.008的⽇期值，调整为0.007。
例如，下⾯的代码在输⼊时，其时间精确度为百分之⼀秒，但经数据库保存后再显⽰出来，其结果就已经做了处理。
DECLARE @t TABLE(date char(21))
INSERT @t SELECT '1900-1-1 00:00:00.000'
...
INSERT @t SELECT '1900-1-1 00:00:00.009'
SELECT date，转换后的⽇期=CAST(date as datetime) FROM @t
/*--结果
date 转换后的⽇期
---------------------------------- ----------------------------
1900-1-1 00:00:00.000 1900-01-01 00:00:00.000
...
1900-1-1 00:00:00.000 1900-01-01 00:00:00.010
--*/
datetime的存储长度为8字节，⽇期和时间各⽤4个字节存储，第⼀个4字节存储⾃1900年1⽉1⽇之前或之后的天数（以1900年1⽉1⽇为分界点，在1900年1⽉1⽇之前的⽇期的天数⼩于0，在1900年1⽉1⽇之后的⽇期的天数⼤于0）。另外⼀个4字节存储以午夜（00:00:00.000）后毫秒数所代表的每天的时间。
例如，下⾯的代码演⽰了datetime变量中，仅包含单纯的⽇期和单纯的时间时，⽇期存储的⼗六进制存储表⽰结果。
DECLARE @dt datetime
--单纯的⽇期
SET @dt='1900-1-2'
SELECT CAST(@dt as binary(8))
--结果: 0x0000000100000000
--单纯的时间
SET @dt='00:00:01'
SELECT CAST(@dt as binary(8))
--结果: 0x000000000000012C
2．smalldatetime
smalldatetime类型处理从1900年1⽉1⽇～2079年6⽉6 ⽇的⽇期和时间数据，精确到分钟。29.998秒或更低的smalldatetime值向下舍⼊为最接近的分钟，29.999秒或更⾼的smalldatetime值向上舍⼊为最接近的分钟。
smalldatetime的存储长度为4字节，第⼀个2字节存储⾃1900年1⽉1⽇之后的天数。另外⼀个2字节存储午夜（00:00:00.000）后的分钟数。
例如，下⾯的代码演⽰了smalldatetime变量中，仅包含单纯的⽇期和单纯的时间时，⽇期存储的⼗六进制存储表⽰结果。
DECLARE @dt smalldatetime
--单纯的⽇期
SET @dt='1900-1-2'
SELECT CAST(@dt as binary(4))
--结果: 0x00010000
--单纯的时间
SET @dt='00:10'
SELECT CAST(@dt as binary(4))
--结果: 0x0000000A
⽇期处理函数
⽇期由年、⽉、⽇、时等多个部分组成，它的处理相对复杂，因此，SQL Server提供了⼤量的⽇期处理函数，⽤以完成各种⽇期数据的处理。掌握好这些函数，对完成数据库的各种⽇期处理⾮常必要，本节将介绍⼏个常⽤的⽇期处理函数。期增减函数可以对⽇期指定部分的值进⾏增减，并返回处理后
的⽇期值，SQL Server提供的⽇期增减函数为DATEADD。
DATEADD的具体语法如下：DATEADD ( datepart , number, date )
其中包括以下参数。
¡ datepart：是规定应向⽇期的哪⼀部分返回新值的参数。表2-1列出了SQL Server⽀持的⽇期部分、缩写及含义。
DATEADD、DATEDIFF⽀持的⽇期部分、缩写及含义
⽇期部分缩写含义
Year yy , yyyy年份
Quarter qq , q季度
Month mm , m⽉份
Dayofyear dy,y⽇
Day dd , d
Week wk , ww星期
Hour Hh⼩时
Minute mi , n分钟
Second ss , s秒
Millisecond Ms毫秒
¡ number：是⽤来增加datepart的值。正数表⽰增加，负数表⽰减少，如果指定的是⾮整数值，则忽略此值的⼩数部分，不做四舍五⼊处理。例如，DATEADD（Day,1.7,date），表⽰date增加1天。
¡ date：是返回datetime或smalldatetime值或⽇期格式字符串的表达式。
如果date是smalldatetime，则返回smalldatetime，否则返回datetime。date为smalldatetime，Datepart为Second（ss,s）或Millisecond（ms）时，返回值将根据⽇期增减的结果调整到分
钟；date为datetime，Datepart为Millisecond（ms）时，返回值将根据⽇期增减的结果调整为百分之三秒。调整规则可以参考2.1节的相关说明。
date允许直接与number进⾏增减计算，即对于DATEADD（Day,number,date），等同于date+number。
⽇期信息获取函数
⽇期信息获取函数⽤于获取⽇期指定部分的相关信息，常⽤的⽇期信息获取函数如表2-2所⽰。
常⽤的⽇期信息获取函数
功能说明语法参数及返回值数据类型说明
返回代表指定⽇期的指定⽇期部分的字符串DATENAME(datepart,date)datepart是指定应返回的⽇期部分的参数，其定义如
表2-3所⽰。date是返回datetime或smalldatetime值
或⽇期格式字符串的表达式。DATENAME函数返
回nvarchar，DATEPART函数返回int
返回代表指定⽇期的指定
⽇期部分的整数
DATEPART(datepart,date)
返回表⽰指定⽇期中的年
份的整数
YEAR(date)返回int
返回表⽰指定⽇期中的⽉
份的整数
MONTH(date)返回int
返回表⽰指定⽇期中的天
的整数
DAY(date)返回int
DATENAME、DATEPART⽀持的⽇期部分、缩写及含义
⽇期部分缩写含义
Year yy , yyyy年份
Quarter qq , q季度
Month mm , m⽉份
Dayofyear dy , y⽇
Day dd , d
Week wk , ww⾃年初开始的第⼏个星期
Weekday Dw星期⼏（例如星期⼀、星期⼆）
Hour Hh⼩时
Minute mi , n分钟
Second ss , s秒。date为smalldatetime时，始终返回0
Millisecond Ms毫秒。date为smalldatetime时，始终返回0，
为datetime时，返回百份之三秒
DATEPART（Week,date）返回的星期计算⽅式，是按照星期⽇为⼀周的第⼀天，这点与中国⼈的⽇期处理习惯不同，在使⽤时要注意这⼀点。DATENAME函数返回指定⽇期的指定⽇期部分的字符串，其返回的具体字符串值，与SET DATEFIRST及SET DATELANGUAGE选项的设置有关。使⽤DATEPART（Weekday,date）时，其返回的值与SET DATEFIRST选项的设置有关，具体的将在2.3节中说明。
⽇期差值计算函数
⽇期差值计算函数⽤于计算两个给定⽇期指定部分的边界数，SQL Server提供的⽇期差值计算函数为DATEDIFF。
DATEDIFF的具体语法如下：
DATEDIFF ( datepart , startdate , enddate )
其中包括以下参数。
¡ datepart：规定了应在⽇期的哪⼀部分计算差额，其定义如表2-1所⽰。
¡ startdate：规定了计算的开始⽇期。
¡ enddate：规定了计算的终⽌⽇期。
返回类型：integer
计算的开始⽇期和终⽌⽇期，可以是⽇期或⽇期格式的字符串。计算的⽅法是从enddate减去startdate。如果startdate⽐enddate晚，返回负值。当结果超出整数值范围，DATEDIFF就产⽣错误。对于毫秒，最⼤数
是24天20⼩时31分钟23.647秒。对于秒，最⼤数是68年。
计算跨分钟、秒和毫秒这些边界的⽅法，使得DATEDIFF给出的结果在全部数据类型中是⼀致的。结果是带正负号的整数值，其等于跨第⼀个和第⼆个⽇期间的datepart边界数。例如，
在2005年1⽉4⽇和2005年2⽉11⽇之间的⽉份数是1。
其他⽇期处理相关函数
其他常⽤的⽇期处理相关函数包括以下⼏个。
1．GETDATE
GETDATE按照datetime值返回当前系统⽇期和时间。
GETDATE的语法如下：
GETDATE()
返回类型：datetime
2．ISDATE
ISDATE确定输⼊的表达式是否有效⽇期。
在输⼊⽇期表达式时，⽇期都是以⽇期格式的字符串提供的，由于不同的区域有不同的⽇期格式，所以并不能保证输⼊的⽇期表达式能够被SQL Server识别，这种情况下，就需要⽤ISDATE来判断⽇期表达式能否正确地被SQL Server识别了。
ISDATE的语法如下：
ISDATE(expression)
返回类型：int
3．CONVERT
CONVERT将某种数据类型的表达式显式转换为另⼀种数据类型。
严格来说，CONVERT不属于⽇期处理函数，只是它被经常⽤于⽇期处理中，所以这⾥把它列⼊了其他⽇期处理函数，下⾯是CONVERT的⽤法描述（只重点说明在⽇期处理中的应⽤）。
CONVERT的具体语法如下：
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
其中包括以下参数。
¡ expression：是要转换数据类型的有效SQL Server表达式。
¡ data_type：是expression转换后的数据类型，length是对于有精度定义需要的data_type的精度定义，对于没有精度定义需要的data_type，该参数可以省略。
¡ style：定义数据类型转换时的格式，对于⽇期类型的转换，它的定义如表2-4所⽰。
表2-4 style在⽇期转换中的说明
不带世纪数位带世纪数位标准输⼊/输出
—0或100默认值mon dd yyyy hh:miAM(或 PM)
1101美国mm/dd/yyyy
dd
3103英国/法国dd/mm/yy
4104德国
5105意⼤利dd-mm-yy
6106—dd mon yy
7107—mon dd, yy
8108—hh:mm:ss
—9或109默认值+毫秒mon dd yyyy hh:mi:ss:mmmAM(或PM)
10110美国mm-dd-yy
11111⽇本yy/mm/dd
12112ISO yymmdd
—13或113欧洲默认值+毫秒dd mon yyyy hh:mm:ss:mmm(24h)
14114—hh:mi:ss:mmm(24h)
—20或120ODBC规范yyyy-mm-dd hh:mm:ss[.fff]
—21或121ODBC规范（带毫秒）yyyy-mm-dd hh:mm:ss[.fff]
—126ISO8601yyyy-mm-ddThh:
—
130Hijri dd mon yyyy hh:mi:ss:mmmAM
—131Hijri dd/mm/yy hh:mi:ss:mmmAM
当从smalldatetime转换为字符数据时，由于smalldatetimer只保存到分钟的数据，因此，对于包含秒或毫秒的样式，将在秒或毫秒的位置上显⽰零。当从datetime或smalldatetime值进⾏转换时，可以通过使⽤适当的char或varchar数据类型长度来截断不需要的⽇期部分。
返回类型：由参数data_type确定。
下⾯是利⽤CONVERT进⾏⽇期转换的简单⽰例：
/*== 字符转换为⽇期时,Style的使⽤ ==*/
--1. Style=101时，表⽰⽇期字符串为:mm/dd/yyyy格式
SELECT CONVERT(datetime,'11/1/2003',101)
--结果:2003-11-01 00:00:00.000
--2. Style=101时，表⽰⽇期字符串为:dd/mm/yyyy格式
SELECT CONVERT(datetime,'11/1/2003',103)
--结果:2003-01-11 00:00:00.000
/*== ⽇期转换为字符串 ==*/
DECLARE @dt datetimestring转date的方法
SET @dt='2003-1-11'
--1. Style=101时，表⽰将⽇期转换为:mm/dd/yyyy 格式
SELECT CONVERT(varchar,@dt,101)
--结果:01/11/2003
--2. Style=103时，表⽰将⽇期转换为:dd/mm/yyyy 格式
SELECT CONVERT(varchar,@dt,103)
--结果:11/01/2003
/*== 这是很多⼈经常犯的错误，对⾮⽇期型转换使⽤⽇期的style样式 ==*/
SELECT CONVERT(varchar,'2003-1-11',101)
--结果:2003-1-11
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论