Oracle 和 Sql Server中日期的显示问题

在日常的项目中，经常遇见User需要显示不同的日期格式。当然，在Oracle和Ms Sql server中，处理日期格式的函数就是两个：TO_Char(Oracle)和Convert(Sql Server).在本文中，对Oracle处理日期格式进行详细介绍，对Sql Server中的处理方式只是蜻蜓点水，做个对照。下面就是一个Sample:要求将日期转化成 "DD MMM YYYY"这种形式显示。

SQL> select to_char(sysdate, 'dd Mon yyyy', 'NLS_DATE_LANGUAGE = AMERICAN')Eng_Show,to_char(sysdate, 'dd Mon yyyy') default_Show   from dual;

ENG_SHOW             DEFAULT_SHOW
-------------------- ----------------
09 Mar 2010          09 3月  2010

一. Format Description:以下来源于Oracle 官方站点。
TO_CHAR(number, format, NLS_Params)
The format mask and the NLS parameters are identical to the TO_NUMBER function.
The NLS parameters again are
NLS_NUMERIC_CHARACTERS -- Specifies characters to use for group separators and the decimal point.
NLS_CURRENCY -- Specifies the local currency.
NLS_ISO_CURRENCY -- Character(s) to represent the ISO currency symbol.
The optional format string you may pass to TO_CHAR() has a number of parameters that affect the string returned by TO_CHAR().

Some of these parameters are listed in the following table.

Parameter Format Examples Description
9 999 Returns digits in specified positions with leading negative sign if the number is negative.
0 0999 9990 0999: Returns a number with leading zeros.9990: Returns a number with trailing zeros.
. 999.99 Returns a decimal point in the specified position.
, 9,999 Returns a comma in the specified position.
$ $999 Returns a leading dollar sign.
B B9.99 If the integer part of a fixed point number is zero, returns spaces for the zeros.
C C999 Returns the ISO currency symbol in the specified position. The symbol comes from the NLS_ISO_CURRENCY parameter.
D 9D99 Returns the decimal point symbol in the specified position. The symbol comes from the NLS_NUMERIC_CHARACTER parameter (default is a period character).
EEEE 9.99EEEE Returns number using the scientific notation.
FM FM90.9 Removes leading and trailing spaces from number.
G 9G999 Returns the group separator symbol in the specified position. The symbol comes from the NLS_NUMERIC_CHARACTER parameter.
L L999 Returns the local currency symbol in the specified position. The symbol comes from the NLS_CURRENCY parameter.
MI 999MI Returns a negative number with a trailing minus sign. Returns a positive number with a trailing space.
PR 999PR Returns a negative number in angle brackets (< >). Returns a positive number with leading and trailing spaces.
RN rn RN rn Returns number as Roman numerals. RN returns uppercase numerals; rn returns lowercase numerals. Number must be an integer between 1 and 3999.
S S999 999S S999: Returns a negative number with a leading negative sign; returns a positive number with a leading positive sign.999S: Returns a negative number with a trailing negative sign; returns a positive number with a trailing positive sign.
TM TM Returns a number using the minimum number of characters. Default is TM9, which returns the number using fixed notation unless the number of characters is greater than 64. If greater than 64, the number is returned using scientific notation.
U U999 Returns the dual currency symbol (Euro, for example) in the specified position. The symbol comes from the NLS_DUAL_CURRENCY parameter.
V 99V99 Returns number multiplied by 10x where x is the number of 9 characters after the V. If necessary, the number is rounded.
X XXXX Returns the number in hexadecimal. If the number is not an integer, the number is rounded to an integer.


Quote from:

Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)

# Paperback: 608 pages

# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)

# Language: English

# ISBN-10: 0072229810

# ISBN-13: 978-0072229813
二. 显示各种形式的日期:

1. [color=red][b]to_char(sysdate,'hh24:mi:ss')[/b][/color]

SQL> select sysdate as today, to_char(sysdate, 'hh24:mi:ss') convert_date from dual;

TODAY       CONVERT_DATE
----------- ------------
3/9/2010 10 22:22:57

2. [color=red][b]to_char(to_date(varchar2,'dd/mm/yyyy'),'is on Day')[/b][/color]

SQL> select sysdate as today ,to_char(to_date('01/01/2010','dd/mm/yyyy'),'"is on "Day') as convert_date from dual;

TODAY       CONVERT_DATE
----------- ---------------
3/9/2010 10 is on 星期五

3. [color=red][b]to_char(sysdate,'DAY dy Dy')[/b][/color]

SQL> select to_char(sysdate,'DAY dy Dy') as day from dual;

DAY
--------------------------------------
星期二 星期二 星期二

4. [color=red][b]to_char(sysdate,'MONTH mon') as month [/b][/color]

SQL> select to_char(sysdate,'MONTH mon') as month from dual;

MONTH
-----------------
3月  3月

5. [color=red][b]to_char(sysdate,'fmMonth ddth, yyyy') [/b][/color]

SQL> select to_char(sysdate,'fmMonth ddth, yyyy') as convert_date from dual;

CONVERT_DATE
-------------------
3月 9th, 2010

SQL Functions and Their Valid NLS Parameters
[b][color=red] SQL Function Valid NLS Parameters [/color][/b]

TO_DATE           NLS_DATE_LANGUAGE
                  NLS_CALENDAR

TO_NUMBER         NLS_NUMERIC_CHARACTERS
                  NLS_CURRENCY
                  NLS_DUAL_CURRENCY
                  NLS_ISO_CURRENCY

TO_CHAR           NLS_DATE_LANGUAGE
                  NLS_NUMERIC_CHARACTERS
                  NLS_CURRENCY
                  NLS_ISO_CURRENCY
                  NLS_DUAL_CURRENCY
                  NLS_CALENDAR

TO_NCHAR          NLS_DATE_LANGUAGE
                  NLS_NUMERIC_CHARACTERS
                  NLS_CURRENCY
                  NLS_ISO_CURRENCY
                  NLS_DUAL_CURRENCY
                  NLS_CALENDAR
NLS_UPPER         NLS_SORT
NLS_LOWER         NLS_SORT
NLS_INITCAP       NLS_SORT
NLSSORT           NLS_SORT

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

不带世纪数位 (yy)	带世纪数位 (yyyy)	
标准	输入/输出**
-	0 或 100 (*)	默认值	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	-	dd mon yy
7	107	-	mon dd, yy
8	108	-	hh:mm:ss
-	9 或 109 (*)	默认值 + 毫秒	mon dd yyyy hh:mi:ss:mmmAM（或 PM）
10	110	美国	mm-dd-yy
11	111	日本	yy/mm/dd
12	112	ISO	yymmdd
-	13 或 113 (*)	欧洲默认值 + 毫秒	dd mon yyyy hh:mm:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 或 120 (*)	ODBC 规范	yyyy-mm-dd hh:mm:ss[.fff]
-	21 或 121 (*)	ODBC 规范（带毫秒）	yyyy-mm-dd hh:mm:ss[.fff]
-	126(***)	ISO8601	yyyy-mm-dd Thh:mm:ss:mmm（不含空格）
-	130*	科威特	dd mon yyyy hh:mi:ss:mmmAM
-	131*	科威特	dd/mm/yy hh:mi:ss:mmmAM

* 默认值（style 0 或 100、9 或 109、13 或 113、20 或 120、21 或 121）始终返回世纪数位 (yyyy)。
** 当转换为 datetime 时输入；当转换为字符数据时输出。
*** 专门用于 XML。对于从 datetime 或 smalldatetime 到 character 数据的转换，输出格式如表中所示。对于从 float、money 或 smallmoney 到 character 数据的转换，输出等同于 style 2。对于从 real 到 character 数据的转换，输出等同于 style 1。

重要 默认情况下，SQL Server 根据截止年份 2049 解释两位数字的年份。即，两位数字的年份 49 被解释为 2049，而两位数字的年份 50 被解释为 1950。许多客户端应用程序（例如那些基于 OLE 自动化对象的客户端应用程序）都使用 2030 作为截止年份。SQL Server 提供一个配置选项（"两位数字的截止年份"），借以更改 SQL Server 所使用的截止年份并对日期进行一致性处理。然而最安全的办法是指定四位数字年份。
当从 smalldatetime 转换为字符数据时，包含秒或毫秒的样式将在这些位置上显示零。当从 datetime 或 smalldatetime 值进行转换时，可以通过使用适当的 char 或 varchar 数据类型长度来截断不需要的日期部分。

下表显示了从 float 或 real 转换为字符数据时的 style 值。

值	        输出
0（默认值）	最大为 6 位数。根据需要使用科学记数法。
1	        始终为 8 位值。始终使用科学记数法。
2	        始终为 16 位值。始终使用科学记数法。

在下表中，左列表示从 money 或 smallmoney 转换为字符数据时的 style 值。

值	        输出
0（默认值）	小数点左侧每三位数字之间不以逗号分隔，小数点右侧取两位数，例如 4235.98。
1	        小数点左侧每三位数字之间以逗号分隔，小数点右侧取两位数，例如 3,510.92。
2	        小数点左侧每三位数字之间不以逗号分隔，小数点右侧取四位数，例如 4235.9819。

使用 CONVERT：
[color=red][b]CONVERT (data_type[(length)], expression [, style])[/b][/color]

select CONVERT(varchar, getdate(), 120 ) 
2004-09-12 11:06:08

select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),\'-\',\'\'),\' \',\'\'),\':\',\'\') 
20040912110608

select CONVERT(varchar(12) , getdate(), 111 ) 
2004/09/12

select CONVERT(varchar(12) , getdate(), 112 ) 
20040912

select CONVERT(varchar(12) , getdate(), 102 ) 
2004.09.12

select CONVERT(varchar(12) , getdate(), 101 ) 
09/12/2004

select CONVERT(varchar(12) , getdate(), 103 ) 
12/09/2004

select CONVERT(varchar(12) , getdate(), 104 ) 
12.09.2004

select CONVERT(varchar(12) , getdate(), 105 ) 
12-09-2004

select CONVERT(varchar(12) , getdate(), 106 ) 
12 09 2004

select CONVERT(varchar(12) , getdate(), 107 ) 
09 12, 2004

select CONVERT(varchar(12) , getdate(), 108 ) 
11:06:08

select CONVERT(varchar(12) , getdate(), 109 ) 
09 12 2004 1

select CONVERT(varchar(12) , getdate(), 110 ) 
09-12-2004

select CONVERT(varchar(12) , getdate(), 113 ) 
12 09 2004 1

select CONVERT(varchar(12) , getdate(), 114 ) 
11:06:08.177