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

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

Sql代码

1. SQL> select to_char(sysdate, 'dd Mon yyyy', 'NLS_DATE_LANGUAGE = AMERICAN')Eng_Show,to_char(sysdate, 'dd Mon yyyy') default_Show   from dual;   
2.     
3. ENG_SHOW             DEFAULT_SHOW   
4. -------------------- ----------------   
5. 09 Mar 2010          09 3月  2010  

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.  to_char(sysdate,'hh24:mi:ss')

Sql代码

1. SQL> select sysdate as today, to_char(sysdate, 'hh24:mi:ss') convert_date from dual;   
2.     
3. TODAY       CONVERT_DATE   
4. ----------- ------------   
5. 3/9/2010 10 22:22:57  

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. to_char(to_date(varchar2,'dd/mm/yyyy'),'is on Day')

Sql代码

1. SQL> select sysdate as today ,to_char(to_date('01/01/2010','dd/mm/yyyy'),'"is on "Day') as convert_date from dual;   
2.     
3. TODAY       CONVERT_DATE   
4. ----------- ---------------   
5. 3/9/2010 10 is on 星期五  

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. to_char(sysdate,'DAY dy Dy')

Sql代码

1. SQL> select to_char(sysdate,'DAY dy Dy') as day from dual;   
2.     
3. DAY  
4. --------------------------------------   
5. 星期二 星期二 星期二  

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

4. to_char(sysdate,'MONTH mon') as month

Sql代码

1. SQL> select to_char(sysdate,'MONTH mon') as month from dual;   
2.     
3. MONTH  
4. -----------------   
5. 3月  3月  

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

5. to_char(sysdate,'fmMonth ddth, yyyy')

Sql代码

1. SQL> select to_char(sysdate,'fmMonth ddth, yyyy') as convert_date from dual;   
2.     
3. CONVERT_DATE   
4. -------------------   
5. 3月 9th, 2010  

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 
           SQL Function      Valid NLS Parameters

Sql代码

1. TO_DATE           NLS_DATE_LANGUAGE   
2.                   NLS_CALENDAR   
3.     
4. TO_NUMBER         NLS_NUMERIC_CHARACTERS   
5.                   NLS_CURRENCY   
6.                   NLS_DUAL_CURRENCY   
7.                   NLS_ISO_CURRENCY   
8.     
9. TO_CHAR           NLS_DATE_LANGUAGE   
10.                   NLS_NUMERIC_CHARACTERS   
11.                   NLS_CURRENCY   
12.                   NLS_ISO_CURRENCY   
13.                   NLS_DUAL_CURRENCY   
14.                   NLS_CALENDAR   
15.     
16. TO_NCHAR          NLS_DATE_LANGUAGE   
17.                   NLS_NUMERIC_CHARACTERS   
18.                   NLS_CURRENCY   
19.                   NLS_ISO_CURRENCY   
20.                   NLS_DUAL_CURRENCY   
21.                   NLS_CALENDAR   
22. NLS_UPPER         NLS_SORT   
23. NLS_LOWER         NLS_SORT   
24. NLS_INITCAP       NLS_SORT   
25. NLSSORT           NLS_SORT  

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_CALENDARNLS_UPPER         NLS_SORTNLS_LOWER         NLS_SORTNLS_INITCAP       NLS_SORTNLSSORT           NLS_SORT

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

Sql代码

1. 不带世纪数位 (yy) 带世纪数位 (yyyy)       
2. 标准  输入/输出**   
3. -   0 或 100 (*) 默认值 mon dd yyyy hh:miAM（或 PM）   
4. 1   101 美国  mm/dd/yyyy   
5. 2   102 ANSI    yy.mm.dd   
6. 3   103 英国/法国   dd/mm/yy   
7. 4   104 德国  dd.mm.yy   
8. 5   105 意大利 dd-mm-yy   
9. 6   106 -   dd mon yy   
10. 7   107 -   mon dd, yy   
11. 8   108 -   hh:mm:ss   
12. -   9 或 109 (*) 默认值 + 毫秒    mon dd yyyy hh:mi:ss:mmmAM（或 PM）   
13. 10  110 美国  mm-dd-yy   
14. 11  111 日本  yy/mm/dd   
15. 12  112 ISO yymmdd   
16. -   13 或 113 (*)    欧洲默认值 + 毫秒  dd mon yyyy hh:mm:ss:mmm(24h)   
17. 14  114 -   hh:mi:ss:mmm(24h)   
18. -   20 或 120 (*)    ODBC 规范 yyyy-mm-dd hh:mm:ss[.fff]   
19. -   21 或 121 (*)    ODBC 规范（带毫秒）    yyyy-mm-dd hh:mm:ss[.fff]   
20. -   126(***)    ISO8601 yyyy-mm-dd Thh:mm:ss:mmm（不含空格）   
21. -   130*    科威特 dd mon yyyy hh:mi:ss:mmmAM   
22. -   131*    科威特 dd/mm/yy hh:mi:ss:mmmAM  

不带世纪数位 (yy)带世纪数位 (yyyy)标准输入/输出**-0 或 100 (*)默认值mon dd yyyy hh:miAM（或 PM）1101美国mm/dd/yyyy2102ANSIyy.mm.dd3103英国/法国dd/mm/yy4104德国dd.mm.yy5105意大利dd-mm-yy6106-dd mon yy7107-mon dd, yy8108-hh:mm:ss-9 或 109 (*)默认值 + 毫秒mon dd yyyy hh:mi:ss:mmmAM（或 PM）10110美国mm-dd-yy11111日本yy/mm/dd12112ISOyymmdd-13 或 113 (*)欧洲默认值 + 毫秒dd mon yyyy hh:mm:ss:mmm(24h)14114-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(***)ISO8601yyyy-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 值。

Sql代码

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

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

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

Sql代码

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

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

使用 CONVERT：
CONVERT (data_type[(length)], expression [, style])

Sql代码

1. select CONVERT(varchar, getdate(), 120 )    
2. 2004-09-12 11:06:08   
3.   
4. select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),\'-\',\'\'),\' \',\'\'),\':\',\'\')    
5. 20040912110608   
6.   
7. select CONVERT(varchar(12) , getdate(), 111 )    
8. 2004/09/12   
9.   
10. select CONVERT(varchar(12) , getdate(), 112 )    
11. 20040912   
12.   
13. select CONVERT(varchar(12) , getdate(), 102 )    
14. 2004.09.12   
15.   
16. select CONVERT(varchar(12) , getdate(), 101 )    
17. 09/12/2004   
18.   
19. select CONVERT(varchar(12) , getdate(), 103 )    
20. 12/09/2004   
21.   
22. select CONVERT(varchar(12) , getdate(), 104 )    
23. 12.09.2004   
24.   
25. select CONVERT(varchar(12) , getdate(), 105 )    
26. 12-09-2004   
27.   
28. select CONVERT(varchar(12) , getdate(), 106 )    
29. 12 09 2004   
30.   
31. select CONVERT(varchar(12) , getdate(), 107 )    
32. 09 12, 2004   
33.   
34. select CONVERT(varchar(12) , getdate(), 108 )    
35. 11:06:08   
36.   
37. select CONVERT(varchar(12) , getdate(), 109 )    
38. 09 12 2004 1   
39.   
40. select CONVERT(varchar(12) , getdate(), 110 )    
41. 09-12-2004   
42.   
43. select CONVERT(varchar(12) , getdate(), 113 )    
44. 12 09 2004 1   
45.   
46. select CONVERT(varchar(12) , getdate(), 114 )    
47. 11:06:08.177  

select CONVERT(varchar, getdate(), 120 ) 2004-09-12 11:06:08select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),\'-\',\'\'),\' \',\'\'),\':\',\'\') 20040912110608select CONVERT(varchar(12) , getdate(), 111 ) 2004/09/12select CONVERT(varchar(12) , getdate(), 112 ) 20040912select CONVERT(varchar(12) , getdate(), 102 ) 2004.09.12select CONVERT(varchar(12) , getdate(), 101 ) 09/12/2004select CONVERT(varchar(12) , getdate(), 103 ) 12/09/2004select CONVERT(varchar(12) , getdate(), 104 ) 12.09.2004select CONVERT(varchar(12) , getdate(), 105 ) 12-09-2004select CONVERT(varchar(12) , getdate(), 106 ) 12 09 2004select CONVERT(varchar(12) , getdate(), 107 ) 09 12, 2004select CONVERT(varchar(12) , getdate(), 108 ) 11:06:08select CONVERT(varchar(12) , getdate(), 109 ) 09 12 2004 1select CONVERT(varchar(12) , getdate(), 110 ) 09-12-2004select CONVERT(varchar(12) , getdate(), 113 ) 12 09 2004 1select CONVERT(varchar(12) , getdate(), 114 ) 11:06:08.177

转载于:https://www.cnblogs.com/Cage/archive/2010/12/03/1895319.html