数据库中的日期转换

数据库中的日期转换

针对于目前常用的数据库Access，SqlServer,Oracle,经常有日期型的数据和字符串之间进行转换，整理如下。

---

1. Access中的日期转换
   
   日期型转换为字符串
   
   Format函数
   

   SELECTFormat(now(),'yyyy/MM/ddHH:mm:ss.SSS')Asdt

   输出结果

   dt
   2007/04/17 18:56:24.2424

   
   
   字符串转换为日期
   
   【yyyy/MM/dd】或者【yyyy-MM-dd】格式的字符串，用DateValue进行转换
   
   SELECTDateValue('2007/04/16')asdt1,DateValue('2007-04-14')Asdt2

   输出结果

   dt1	dt2
   2007/04/16	2007/04/14

   
   【yyyyMMdd】格式的字符串，需要先转换为【yyyy/MM/dd】格式再用DateValue转换
   
   SELECTDateValue(Mid('20070416',1,4)&'-'&Mid('20070416',5,2)&'-'&Mid('20070416',7,2))asdt

   输出结果

   dt
   2007/04/16
   2007/04/16
   2007/04/16

   
   

2. SqlServer中的日期转换
   
   在SqlServer中，与Access Format（），DateValue()函数相对应的是Convert（）和Cast（）函数，方括号内是可选项,[ style ]的表示形式参照下面的一览表。

   SyntaxforCAST:
   CAST(expressionASdata_type[(length)])
   
   SyntaxforCONVERT:
   CONVERT(data_type[(length)],expression[,style])
   
   

   在下表中，左侧的两列表示将 datetime 或 smalldatetime 数据转换为字符数据的 style 值。将 style 值加 100，可获得包括世纪数位的四位年份 (yyyy)。

   不带世纪数位 (yy) (1)	带世纪数位 (yyyy)	标准	输入/输出 (3)
   -	0 或 100 (1,2)	默认设置	mon dd yyyy hh:miAM（或 PM）
   1	101	美国	mm/dd/yyyy
   2	102	ANSI	yy.mm.dd
   3	103	英国/法国	dd/mm/yy
   4	104	德国	dd.mm.yy
   5	105	意大利	dd-mm-yy
   6	106 (1)	-	dd mon yy
   7	107 (1)	-	mon dd, yy
   8	108	-	hh:mi:ss
   -	9 或 109 (1,2)	默认设置 + 毫秒	mon dd yyyy hh:mi:ss:mmmAM（或 PM）
   10	110	美国	mm-dd-yy
   11	111	日本	yy/mm/dd
   12	112	ISO	yymmdd
   -	13 或 113 (1,2)	欧洲默认设置 + 毫秒	dd mon yyyy hh:mi:ss:mmm(24h)
   14	114	-	hh:mi:ss:mmm(24h)
   -	20 或 120 (2)	ODBC 规范	yyyy-mm-dd hh:mi:ss(24h)
   -	21 或 121 (2)	ODBC 规范（带毫秒）	yyyy-mm-dd hh:mi:ss.mmm(24h)
   -	126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm（无空格）
   	127(6, 7)	带时区 Z 的 ISO8601。	yyyy-mm-ddThh:mi:ss.mmmZ （无空格）
   -	130 (1,2)	回历 (5)	dd mon yyyy hh:mi:ss:mmmAM
   -	131 (2)	回历 (5)	dd/mm/yy hh:mi:ss:mmmAM

   ¹ 这些样式值将返回不确定的结果。包括所有 (yy)（不带世纪数位）样式和一部分 (yyyy)（带世纪数位）样式。
   ^2. 默认值（style 0 或 100、9 或 109、13 或 113、20 或 120 以及 21 或 121）始终返回世纪数位 (yyyy)。
   ³ 转换为 datetime 时输入；转换为字符数据时输出。
   ⁴ 为用于 XML 而设计。对于从 datetime 或 smalldatetime 到字符数据的转换，其输出格式如上一个表所述。
   ^5. 回历是有多种变体的日历系统。SQL Server 2005 使用科威特算法。
   
   日期转换为字符串
   
   Convert()函数

   SelectConvert(nchar,getdate(),126)asdt

   输出结果：
   2007-04-18T10:18:17.130
   
   或者用 Cast()函数

   SelectCast(getdate()asvarchar)asdt

   输出结果：
   04 18 2007 10:23AM
   
   字符串转换为日期
   
   Convert()函数

   SelectConvert(datetime,'2007/04/18')asdt

   输出结果：
   2007-04-18 00:00:00.000
   
   Cast()函数

   SelectCast('2007/04/18'asdatetime)asdt

   输出结果：
   2007-04-18 00:00:00.000
   
   综合来说，Convert()函数用起来要方便一些。

3. Oracle中的日期转换
   
   与之相对应的是To_Char()和To_Date()函数
   
   日期转换为字符串
   
   
   selectto_char(sysdate,'yyyy/MM/ddhh24:mm:ss')as dt fromdual;
   
   【hh24】表示时间是24小时制，【hh】表示12小时制
   
   selectto_char(sysdate,'hh')as hour12, to_char(sysdate,'hh24')as hour24
   
   
   字符串转换为日期
   
   SelectTO_DATE('2007-04-18','yyyy-mm-dd')asdt
   
   如果想设置日期的语言，需要加上 'NLS_DATE_LANGUAGE = American'参数
   
   SelectTO_DATE('2002-08-26','YYYY-mm-dd','NLS_DATE_LANGUAGE=American')asdt

数据库中的日期转换

针对于目前常用的数据库Access，SqlServer,Oracle,经常有日期型的数据和字符串之间进行转换，整理如下。

---

1. Access中的日期转换
   
   日期型转换为字符串
   
   Format函数
   

   SELECTFormat(now(),'yyyy/MM/ddHH:mm:ss.SSS')Asdt

   输出结果

   dt
   2007/04/17 18:56:24.2424

   
   
   字符串转换为日期
   
   【yyyy/MM/dd】或者【yyyy-MM-dd】格式的字符串，用DateValue进行转换
   
   SELECTDateValue('2007/04/16')asdt1,DateValue('2007-04-14')Asdt2

   输出结果

   dt1	dt2
   2007/04/16	2007/04/14

   
   【yyyyMMdd】格式的字符串，需要先转换为【yyyy/MM/dd】格式再用DateValue转换
   
   SELECTDateValue(Mid('20070416',1,4)&'-'&Mid('20070416',5,2)&'-'&Mid('20070416',7,2))asdt

   输出结果

   dt
   2007/04/16
   2007/04/16
   2007/04/16

   
   

2. SqlServer中的日期转换
   
   在SqlServer中，与Access Format（），DateValue()函数相对应的是Convert（）和Cast（）函数，方括号内是可选项,[ style ]的表示形式参照下面的一览表。

   SyntaxforCAST:
   CAST(expressionASdata_type[(length)])
   
   SyntaxforCONVERT:
   CONVERT(data_type[(length)],expression[,style])
   
   

   在下表中，左侧的两列表示将 datetime 或 smalldatetime 数据转换为字符数据的 style 值。将 style 值加 100，可获得包括世纪数位的四位年份 (yyyy)。

   不带世纪数位 (yy) (1)	带世纪数位 (yyyy)	标准	输入/输出 (3)
   -	0 或 100 (1,2)	默认设置	mon dd yyyy hh:miAM（或 PM）
   1	101	美国	mm/dd/yyyy
   2	102	ANSI	yy.mm.dd
   3	103	英国/法国	dd/mm/yy
   4	104	德国	dd.mm.yy
   5	105	意大利	dd-mm-yy
   6	106 (1)	-	dd mon yy
   7	107 (1)	-	mon dd, yy
   8	108	-	hh:mi:ss
   -	9 或 109 (1,2)	默认设置 + 毫秒	mon dd yyyy hh:mi:ss:mmmAM（或 PM）
   10	110	美国	mm-dd-yy
   11	111	日本	yy/mm/dd
   12	112	ISO	yymmdd
   -	13 或 113 (1,2)	欧洲默认设置 + 毫秒	dd mon yyyy hh:mi:ss:mmm(24h)
   14	114	-	hh:mi:ss:mmm(24h)
   -	20 或 120 (2)	ODBC 规范	yyyy-mm-dd hh:mi:ss(24h)
   -	21 或 121 (2)	ODBC 规范（带毫秒）	yyyy-mm-dd hh:mi:ss.mmm(24h)
   -	126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm（无空格）
   	127(6, 7)	带时区 Z 的 ISO8601。	yyyy-mm-ddThh:mi:ss.mmmZ （无空格）
   -	130 (1,2)	回历 (5)	dd mon yyyy hh:mi:ss:mmmAM
   -	131 (2)	回历 (5)	dd/mm/yy hh:mi:ss:mmmAM

   ¹ 这些样式值将返回不确定的结果。包括所有 (yy)（不带世纪数位）样式和一部分 (yyyy)（带世纪数位）样式。
   ^2. 默认值（style 0 或 100、9 或 109、13 或 113、20 或 120 以及 21 或 121）始终返回世纪数位 (yyyy)。
   ³ 转换为 datetime 时输入；转换为字符数据时输出。
   ⁴ 为用于 XML 而设计。对于从 datetime 或 smalldatetime 到字符数据的转换，其输出格式如上一个表所述。
   ^5. 回历是有多种变体的日历系统。SQL Server 2005 使用科威特算法。
   
   日期转换为字符串
   
   Convert()函数

   SelectConvert(nchar,getdate(),126)asdt

   输出结果：
   2007-04-18T10:18:17.130
   
   或者用 Cast()函数

   SelectCast(getdate()asvarchar)asdt

   输出结果：
   04 18 2007 10:23AM
   
   字符串转换为日期
   
   Convert()函数

   SelectConvert(datetime,'2007/04/18')asdt

   输出结果：
   2007-04-18 00:00:00.000
   
   Cast()函数

   SelectCast('2007/04/18'asdatetime)asdt

   输出结果：
   2007-04-18 00:00:00.000
   
   综合来说，Convert()函数用起来要方便一些。

3. Oracle中的日期转换
   
   与之相对应的是To_Char()和To_Date()函数
   
   日期转换为字符串
   
   
   selectto_char(sysdate,'yyyy/MM/ddhh24:mm:ss')as dt fromdual;
   
   【hh24】表示时间是24小时制，【hh】表示12小时制
   
   selectto_char(sysdate,'hh')as hour12, to_char(sysdate,'hh24')as hour24
   
   
   字符串转换为日期
   
   SelectTO_DATE('2007-04-18','yyyy-mm-dd')asdt
   
   如果想设置日期的语言，需要加上 'NLS_DATE_LANGUAGE = American'参数
   
   SelectTO_DATE('2002-08-26','YYYY-mm-dd','NLS_DATE_LANGUAGE=American')asdt