`

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.  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


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 星期五


3. to_char(sysdate,'DAY dy Dy')

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月


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 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


对比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:
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
1
0
分享到:
评论
1 楼 25627380 2010-08-20  
很强悍,我喜欢:
to_char(last_cal_date, 'DD Mon YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')


这个在我的项目里有用到,谢谢!

相关推荐

    ORACLE与SQLSERVER中的语法区别

    在数据库管理系统中,ORACLE 和 SQL Server 是两个常用的关系数据库管理系统。虽然它们都使用 SQL 语言,但是它们之间存在一些语法上的区别。这篇文章将详细介绍 ORACLE 和 SQL Server 中的语法区别,并提供一些实用...

    Oracle数据库导入到SqlServer步骤(图文篇)

    本文将详细介绍如何将 Oracle 数据库导入到 SqlServer 中。 Step 1-6: 数据源设置 首先,在要导入的数据库上右键,点击“任务”-“导入数据”。然后,在“导入导出向导”中点击“下一步”。接着,选择数据源为...

    oracle,sqlserver及mysql对比

    Oracle、SQL Server 及 MySQL 对比 Oracle、SQL Server 及 MySQL 是目前最流行的三大关系数据库管理系统,各有其优缺点。本文将从表空间、查询语句、事务、分区表和分区索引、优缺点等方面对三者进行比较。 表空间...

    实现ORACLE与SQLSERVER数据库间的数据抽取与转换工具

    总结来说,实现“Oracle与SQLServer数据库间的数据抽取与转换工具”涉及的主要知识点包括:数据库连接、SQL查询、数据转换规则、多数据库系统间的语法差异理解和编程实现。这个工具的源代码压缩包"复件 zExtractor...

    oracle 和sql Server 函数对比

    在IT行业中,数据库管理系统(DBMS)是至关重要的组成部分,其中Oracle和SQL Server是最为广泛使用的两种DBMS。它们各自提供了丰富的内置函数来处理数据,包括数值、字符串、日期时间等类型的操作。本文将深入探讨...

    sqlserver-oracle 数据类型对照

    - `datetime`和`smalldatetime`在SQL Server中对应Oracle的`DATE`,表示日期和时间。 - `decimal`和`numeric`在SQL Server中对应Oracle的`NUMBER(precision)`,其中precision是精度。 - `float`在SQL Server中分...

    oracle和sqlserver数据迁移VB代码

    总的来说,Oracle到SQL Server的数据迁移和CSV文件的备份还原是IT领域中常见的任务,VB作为一种强大的编程语言,提供了丰富的工具和类库来处理这些问题。通过编写合适的VB代码,我们可以实现高效、可靠的数据库迁移...

    ORACLE与SQLSERVER语法差异分析

    在数据库管理领域,ORACLE和SQL SERVER是两个广泛使用的数据库管理系统,它们在语法上有许多相似之处,但也存在一些显著的差异。以下是对标题和描述中所述知识点的详细说明: 1. **递归查询**: - 在SQL SERVER中...

    ORACLE与SQLSERVER函数异同比较

    ### ORACLE与SQLSERVER函数异同比较 在数据库领域,Oracle和SQL Server是两种非常流行的数据库管理系统。这两种系统虽然有着不同的背景和发展历程,但都提供了丰富的功能来满足各种业务需求。尤其是在函数支持方面...

    sql server 和oracle 中数据类型的区别

    5. **datetime**: 存储日期和时间信息,SQL Server中`datetime`类型占用8个字节,Oracle中的对应类型为`DATE`。 6. **decimal**: 存储十进制数,如`decimal(9)`,在Oracle中对应`NUMBER(18)`。 7. **float**: 浮点数...

    Oracle和SQL Server浅析

    至于日期,Oracle 和 SQL Server 都有自己的日期函数,如 Oracle 的 `SYSDATE` 和 SQL Server 的 `GETDATE()`,以及日期格式化方法。在日期处理方面,Oracle 使用 `TO_CHAR` 转换日期格式,SQL Server 使用 `CONVERT...

    从SQLServer迁移大批量数据到Oracle

    随着业务的发展和技术的进步,企业可能需要将现有的 SQL Server 数据库迁移到 Oracle 数据库。本文将详细介绍从 SQL Server 迁移大批量数据到 Oracle 的方法和具体的操作步骤。 #### 关键技术与工具 - **SQL ...

    SqlServer转换Oracle.docx

    本文将围绕《SqlServer转换Oracle.docx》文档中的关键知识点进行详细解析,帮助读者更好地理解和掌握SQL Server到Oracle迁移过程中的技术要点。 #### 二、连接字符串与数据源处理 在文档中提到了一种获取连接字符串...

    sqlserver转oracle

    标题“sqlserver转oracle”指的是将SQL Server数据库中的数据迁移或同步到Oracle数据库的过程。这个过程通常发生在组织更换数据库系统,或者需要在不同数据库平台之间共享数据时。下面将详细介绍这个过程中涉及的...

    异种数据库函数和标准SQL语法比较Oracle与SqlServer.rar

    在IT行业中,数据库管理系统(DBMS)是至关重要的工具,其中Oracle和SQL Server是两个广泛使用的主流系统。本文将深入探讨这两个数据库系统中异种函数和标准SQL语法的差异,帮助用户更好地理解和应用它们。 首先,...

    ORACLE与SQL SERVER语言的比较.pdf

    ORACLE和SQL SERVER都支持多种数据类型,包括数字、字符、日期、时间、Interval等,但是它们之间也有一些差异。例如,ORACLE的NUMBER类型可以存储很大的数值,而SQL SERVER的INT类型可以存储更大的整数值。 3.查询...

    SqlServer和Oracle的不同的一些体会

    ### SqlServer和Oracle的不同的一些体会 #### 一、概述 在进行数据库移植的过程中,往往会遇到多种数据库间的迁移问题。本文将基于一次从Sql Server到Oracle的数据库移植经历,分享一些重要的区别与注意事项,旨在...

    Sql语法转换为Oracle语法

    10. 日期格式:SQL Server和Oracle默认的日期格式不同,转换时需要考虑日期字符串的格式化问题。 在Delphi项目中,"sqlconvert.dpr" 是项目的主程序文件,"Unit1.dfm" 和 "Unit1.pas" 可能是用户界面和其对应的逻辑...

    oracle与sqlserver日期函数对比.docx

    Oracle 和 SQL Server 都是广泛使用的数据库管理系统,它们在处理日期和时间数据时提供了丰富的内置函数。下面是对这两个系统中日期函数的详细比较: 1. **日期相加/减**: - Oracle 使用 `ADD_MONTHS(date, ...

    oracle与sqlserver日期函数对比.pdf

    Oracle 和 Microsoft SQL Server 都是广泛使用的数据库管理系统,它们都提供了丰富的日期和时间处理功能。在对比这些函数时,我们可以看到两者在处理日期操作时有相似之处,但也存在一些不同。 1. **日期相加与减**...

Global site tag (gtag.js) - Google Analytics