ORACLE to_char(),to_date()用法

to_date() function

1.日期格式参数 含义说明 

D 一周中的星期几 

DAY 天的名字，使用空格填充到9个字符 

DD 月中的第几天 

DDD 年中的第几天 

DY 天的简写名 

IW ISO标准的年中的第几周 

IYYY ISO标准的四位年份 

YYYY 四位年份 

YYY,YY,Y 年份的最后三位，两位，一位 

HH 小时，按12小时计 

HH24 小时，按24小时计 

MI 分 

SS 秒 

MM 月 

Mon 月份的简写 

Month 月份的全名 

W 该月的第几个星期 

WW 年中的第几个星期    

 

2.日期时间间隔操作

当前时间减去7分钟的时间

select sysdate,sysdate - interval '7' MINUTE from dual

当前时间减去7小时的时间

select sysdate - interval '7' hour from dual

当前时间减去7天的时间

select sysdate - interval '7' day from dual

当前时间减去7月的时间

select sysdate,sysdate - interval '7' month from dual

当前时间减去7年的时间

select sysdate,sysdate - interval '7' year from dual

时间间隔乘以一个数字

select sysdate,sysdate - 8 *interval '2' hour from dual

 

 

to_char() function

The following are number examples for the to_char function.

to_char(1210.73, '9999.9')	would return '1210.7'
to_char(1210.73, '9,999.99')	would return '1,210.73'
to_char(1210.73, '$9,999.00')	would return '$1,210.73'
to_char(21, '000099')	would return '000021'

 

The following is a list of valid parameters when the to_char function is used to convert a date to a string. These parameters can be used in many combinations.

 

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year ｂａｓｅd on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) ｂａｓｅd on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds.

The following are date examples for the to_char function.

to_char(sysdate, 'yyyy/mm/dd');	would return '2003/07/09'
to_char(sysdate, 'Month DD, YYYY');	would return 'July 09, 2003'
to_char(sysdate, 'FMMonth DD, YYYY');	would return 'July 9, 2003'
to_char(sysdate, 'MON DDth, YYYY');	would return 'JUL 09TH, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');	would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');	would return 'Jul 9th, 2003'

You will notice that in some examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.

to_char(sysdate, 'FMMonth DD, YYYY');	would return 'July 9, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');	would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');	would return 'Jul 9th, 2003'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

关于format_mask parameter 中的FM 自己来个例子

 

SQL> select to_char(sysdate, 'FMMonth DD, YYYY') from dual;

TO_CHAR(SYSDATE,'FMMONTHDD,YYYY')
------------------------------------
November 6, 2009

SQL> select to_char(sysdate, 'Month DD, YYYY') from dual;

TO_CHAR(SYSDATE,'MONTHDD,YYYY')
------------------------------------
November  06, 2009

 

 

使用to_char()来转换数字时候参数的问题

TO_CHAR ( n [, fmt [, 'nlsparam']] )

该函数将NUMBER类型的n按数值格式fmt转换成VARCHAR2类型的值。'nlsparams'指定由数值格式的元素返回的字符,包括:

．小数点字符

．组分隔符

．本地钱币符号

．国际钱币符号

变元的形式为：

'NLS_NUMERIC_CHARACTERS="dg" NLS_CURRENCY="tcxt" NLS_ISO_CURRENCY=territory'

其中d为小数点字符,g为组分隔符。

例 :TO_CHAR (17145,'L099G999','NLS_NUMERIC_CHARACTERS=".," NLS_CURRENCY="NUD"')

结果为NUD017,145

 

其中用到的几个参数的解释如下

 

NLS_ISO_CURRENCY

Property	Description
Parameter type	String
Syntax	NLS_ISO_CURRENCY = territory
Default value	Derived from NLS_TERRITORY
Modifiable	ALTER SESSION
Range of values	Any valid NLS_TERRITORY value

NLS_ISO_CURRENCY specifies the string to use as the international currency symbol for the C number format element.

 

 

NLS_TERRITORY

Property	Description
Parameter type	String
Syntax	NLS_TERRITORY = territory
Default value	Operating system-dependent
Modifiable	ALTER SESSION
Range of values	Any valid territory name
Basic	Yes

NLS_TERRITORY specifies the name of the territory whose conventions are to be followed for day and week numbering.

This parameter also establishes the default date format, the default decimal character and group separator, and the default ISO and local currency symbols.

 

 

NLS_CURRENCY

Property	Description
Parameter type	String
Syntax	NLS_CURRENCY = currency_symbol
Default value	Derived from NLS_TERRITORY
Modifiable	ALTER SESSION
Range of values	Any valid character string, with a maximum of 10 bytes (not including null)
Basic	No

NLS_CURRENCY specifies the string to use as the local currency symbol for the L number format element. The default value of this parameter is determined by NLS_TERRITORY.

 

 

其中有9的地方如果有数字就显示如果没有数字就不显示，有0的地方在没有数字的时候也会有0来占位。

下面再举个简单的例子：

SQL> select to_char(1.234,'99.999') from dual;

TO_CHAR
-------
  1.234

SQL>
SQL> select to_char(1.234,'09.999') from dual;

TO_CHAR
-------
 01.234

 

 

 

关于oracle的trunc与round处理日期和数值的区别

 

trunc(date,[fmt])   
  解释：返回截取时间部分后的DATE，如果有fmt部分的设置，则返回与该部分最近的日期。

round(date,[fmt])   
  解释：返回将日期date舍入后的DATE，如果有fmt部分的设置，则按照fmt指定的格式舍入返回DATE。   
 

一、用trunc函数处理日期

 日期用例 '2008-11-28 12:59:59'周五  

1.没有fmt部分时

   语句： SELECT   TRUNC(TO_DATE('2008-11-28 12:00:01','YYYY-MM-DD hh24:mi:ss')) FROM   DUAL;
          结果： 2008-11-28  

        

2.得到最当前日期之前的最近的一个周日的日期

   语句： SELECT   TRUNC(TO_DATE('2008-11-28 12:59:59','YYYY-MM-DD hh24:mi:ss'),'D') FROM   DUAL;   
   结果： 2008-11-23 周日

   语句： SELECT   TRUNC(TO_DATE('2008-11-28 12:59:59','YYYY-MM-DD hh24:mi:ss'),'D')+1 FROM   DUAL;   
   结果： 2008-11-24 周一

 

3.得到最当前日期的所在月份的第一天

   语句： SELECT   TRUNC(TO_DATE('2008-11-28 12:59:59','YYYY-MM-DD hh24:mi:ss'),'MM') FROM   DUAL;   
   结果： 2008-11-1

   语句： SELECT   TRUNC(TO_DATE('2008-11-28 12:59:59','YYYY-MM-DD hh24:mi:ss'),'MM')-1 FROM   DUAL; 

   结果： 2008-10-31

 

4.得到最当前日期的所在年份的第一天

   语句： SELECT   TRUNC(TO_DATE('2008-11-28 12:59:59','YYYY-MM-DD hh24:mi:ss'),'Y') FROM   DUAL;   
   结果： 2008-1-1

   语句： SELECT   TRUNC(TO_DATE('2008-11-28 12:59:59','YYYY-MM-DD hh24:mi:ss'),'Y')-1 FROM   DUAL; 

   结果： 2007-12-31

 

SQL>

ROUND(-4.5767,1) TRUNC(-4.5767,1)
---------------- ----------------
            -4.6             -4.5

ROUND(4.5767,1) TRUNC(4.5767,1)
--------------- ---------------
            4.6             4.5

ROUND(14.5767,-1) TRUNC(14.5767,-1)
----------------- -----------------
               10                10

 

关于日期的比较

SQL>

TRUNC(SYSDATE,'YEAR') ROUND(SYSDATE,'YEAR')
--------------------- ---------------------
2005-1-1              2006-1-1

TRUNC(SYSDATE,'MONTH') ROUND(SYSDATE,'MONTH')
---------------------- ----------------------
2005-8-1               2005-9-1

TRUNC(SYSDATE,'DAY') ROUND(SYSDATE,'DAY')
-------------------- --------------------
2005-8-21            2005-8-21

TRUNC(SYSDATE,'HH') ROUND(SYSDATE,'HH')
------------------- -------------------
2005-8-23 10:00:00 2005-8-23 10:00:00

 

呼呼~又是一篇长长的文章啊。网上搜了些资料，自己做了点实验。记下来，方便以后查询用，特别是那些函数的参数格式。