trunc()是截取整数部分,如 select trunc(59.232) from dual;返回:59nvl()是如果值为null时的缺省值,如 select nvl(field, 0) from table;如果field为空,则返回0
SQL> select trunc(123.23) from dual;TRUNC(123.23) ------------- 123SQL> select nvl(null,'一个空值') from dual;NVL(NULL,'一个空值') -------------------- 一个空值trunc--取整函数 nvl --操作数1为空,那就取第二个值,反之,显示操作数1
首先给您讲清Trunc函数所有功能, 1.截位数字 2.截位日期 实例如下: 1. SELECT TRUNC(15.79,1) FROM DUAL;TRUNC(15.79,1) ---------- 15.7SELECT TRUNC(15.79,-1) FROM DUAL;TRUNC(15.79,-1) ---------- 10 2. SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR') "New Year" FROM DUAL;
New Year --------- 01-JAN-92 给出截日期的格式: Date Format Models for the ROUND and TRUNC Date Functions Format Model Rounding or Truncating Unit CC One greater than the first two digits of a four-digit year. SCC
SYYYY Year (rounds up on July 1) YYYY YEAR SYEAR YYY YY YIYYY ISO Year IY IY IQ Quarter (rounds up on the sixteenth day of the second month of the quarter) MONTH Month (rounds up on the sixteenth day) MON MM RM
WW Same day of the week as the first day of the year. IW Same day of the week as the first day of the ISO year. W Same day of the week as the first day of the month. DDD Day DD JDAY Starting day of the week DY D
select trunc(59.232) from dual;返回:59nvl()是如果值为null时的缺省值,如
select nvl(field, 0) from table;如果field为空,则返回0
-------------
123SQL> select nvl(null,'一个空值') from dual;NVL(NULL,'一个空值')
--------------------
一个空值trunc--取整函数
nvl --操作数1为空,那就取第二个值,反之,显示操作数1
1.截位数字
2.截位日期
实例如下:
1.
SELECT TRUNC(15.79,1) FROM DUAL;TRUNC(15.79,1)
----------
15.7SELECT TRUNC(15.79,-1) FROM DUAL;TRUNC(15.79,-1)
----------
10
2.
SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR') "New Year" FROM DUAL;
New Year
---------
01-JAN-92 给出截日期的格式:
Date Format Models for the ROUND and TRUNC Date Functions
Format Model Rounding or Truncating Unit
CC One greater than the first two digits of a four-digit year.
SCC
SYYYY Year (rounds up on July 1)
YYYY
YEAR
SYEAR
YYY
YY
YIYYY ISO Year
IY
IY
IQ Quarter (rounds up on the sixteenth day of the second month of the quarter) MONTH Month (rounds up on the sixteenth day)
MON
MM
RM
WW Same day of the week as the first day of the year. IW Same day of the week as the first day of the ISO year. W Same day of the week as the first day of the month. DDD Day
DD
JDAY Starting day of the week
DY
D
HH Hour
HH12
HH24MI Minute