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 | Last 3, 2, or 1 digit(s) of year. |
IYY | Last 3, 2, or 1 digit(s) of ISO year. |
IYYY | 4-digit year based 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) based 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
其中用到的几个参数的解释如下
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
呼呼~又是一篇长长的文章啊。网上搜了些资料,自己做了点实验。记下来,方便以后查询用,特别是那些函数的参数格式。