Oracle 和 Sql Server中日期的显示问题
在日常的项目中,经常遇见User需要显示不同的日期格式。当然,在Oracle和Ms Sql server中,处理日期格式的函数就是两个:TO_Char(Oracle)和Convert(Sql Server).在本文中,对Oracle处理日期格式进行详细介绍,对Sql Server中的处理方式只是蜻蜓点水,做个对照。下面就是一个Sample:要求将日期转化成 "DD MMM YYYY"这种形式显示。
一. 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')
2. to_char(to_date(varchar2,'dd/mm/yyyy'),'is on Day')
3. to_char(sysdate,'DAY dy Dy')
4. to_char(sysdate,'MONTH mon') as month
5. to_char(sysdate,'fmMonth ddth, yyyy')
SQL Functions and Their Valid NLS Parameters
SQL Function Valid NLS Parameters
对比Sql Server的Convert函数:
在下表中,左侧的两列表示将 datetime 或 smalldatetime 转换为字符数据的 style 值。给 style 值加 100,可获得包括世纪数位的四位年份 (yyyy)。
* 默认值(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 值。
在下表中,左列表示从 money 或 smallmoney 转换为字符数据时的 style 值。
使用 CONVERT:
CONVERT (data_type[(length)], expression [, style])
- 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
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> select sysdate as today, to_char(sysdate, 'hh24:mi:ss') convert_date from dual;
- TODAY CONVERT_DATE
- ----------- ------------
- 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> 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 星期五
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> select to_char(sysdate,'DAY dy Dy') as day from dual;
- DAY
- --------------------------------------
- 星期二 星期二 星期二
SQL> select to_char(sysdate,'DAY dy Dy') as day from dual; DAY--------------------------------------星期二 星期二 星期二
4. to_char(sysdate,'MONTH mon') as month
- SQL> select to_char(sysdate,'MONTH mon') as month from dual;
- MONTH
- -----------------
- 3月 3月
SQL> select to_char(sysdate,'MONTH mon') as month from dual; MONTH-----------------3月 3月
5. to_char(sysdate,'fmMonth ddth, yyyy')
- SQL> select to_char(sysdate,'fmMonth ddth, yyyy') as convert_date from dual;
- CONVERT_DATE
- -------------------
- 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
- 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
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)。
- 不带世纪数位 (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
不带世纪数位 (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 值。
- 值 输出
- 0(默认值) 最大为 6 位数。根据需要使用科学记数法。
- 1 始终为 8 位值。始终使用科学记数法。
- 2 始终为 16 位值。始终使用科学记数法。
值 输出0(默认值)最大为 6 位数。根据需要使用科学记数法。1 始终为 8 位值。始终使用科学记数法。2 始终为 16 位值。始终使用科学记数法。
在下表中,左列表示从 money 或 smallmoney 转换为字符数据时的 style 值。
- 值 输出
- 0(默认值) 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取两位数,例如 4235.98。
- 1 小数点左侧每三位数字之间以逗号分隔,小数点右侧取两位数,例如 3,510.92。
- 2 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取四位数,例如 4235.9819。
值 输出0(默认值)小数点左侧每三位数字之间不以逗号分隔,小数点右侧取两位数,例如 4235.98。1 小数点左侧每三位数字之间以逗号分隔,小数点右侧取两位数,例如 3,510.92。2 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取四位数,例如 4235.9819。
使用 CONVERT:
CONVERT (data_type[(length)], expression [, style])
- 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
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