数据库中的日期转换

数据库中的日期转换

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

---

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

   SELECT Format(now(), ' yyyy/MM/ddHH:mm:ss.SSS ' ) As dt

   输出结果

   dt
   2007/04/17 18:56:24.2424

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

   输出结果

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

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

   输出结果

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

   
   

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

   Syntax for CAST :
   CAST (expression AS data_type [ (length) ] )
   
   Syntax for CONVERT :
   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()函数

   Select Convert ( nchar , getdate (), 126 ) as dt

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

   Select Cast ( getdate () as varchar ) as dt

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

   Select Convert ( datetime , ' 2007/04/18 ' ) as dt

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

   Select Cast ( ' 2007/04/18 ' as datetime ) as dt

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

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

数据库中的日期转换

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

---

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

   SELECT Format(now(), ' yyyy/MM/ddHH:mm:ss.SSS ' ) As dt

   输出结果

   dt
   2007/04/17 18:56:24.2424

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

   输出结果

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

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

   输出结果

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

   
   

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

   Syntax for CAST :
   CAST (expression AS data_type [ (length) ] )
   
   Syntax for CONVERT :
   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()函数

   Select Convert ( nchar , getdate (), 126 ) as dt

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

   Select Cast ( getdate () as varchar ) as dt

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

   Select Convert ( datetime , ' 2007/04/18 ' ) as dt

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

   Select Cast ( ' 2007/04/18 ' as datetime ) as dt

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

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