Oracle Function-NEXT_DAY,TRUNC,ROUND,CEIL,SIGN

注意:国际通用的日期中；周末才是一个周的第一天

ROUND and TRUNC Date Functions
next lists the format models you can use with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.
Date Format Models for the ROUND and TRUNC Date Functions ,Format Model Rounding or Truncating Unit

CC
SCC

One greater than the first two digits of a four-digit year

SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y

Year (rounds up on July 1)(up表示+1)

SELECT ROUND(SYSDATE, 'SYYYY'),
       ROUND(SYSDATE, 'YYYY'),
       ROUND(SYSDATE, 'YEAR'),
       ROUND(SYSDATE, 'SYEAR'),
       ROUND(SYSDATE, 'YY'),
       ROUND(SYSDATE, 'Y')
  FROM DUAL

IYYY
IY
IY
I

 

ISO Year

Q

 

Quarter (rounds up on the sixteenth day of the second month of the quarter)

MONTH
MON
MM
RM

 

Month (rounds up on the sixteenth day)

（超过每个月的十五号都会四舍五入到下个月的1号）

SELECT ROUND(SYSDATE + 15, 'MONTH'),
       ROUND(SYSDATE + 15, 'MON'),
       ROUND(SYSDATE + 15, 'MM'),
       ROUND(SYSDATE + 15, 'RM'),
       SYSDATE 系统当前时间
  FROM DUAL

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

 

Day

DAY
DY
D

 

Starting day of the week

HH
HH12
HH24

 

Hour

MI

 

Minute

-------------------------------------------------------------------------------------------------------------

NEXT_DAY()函数的用法：

NEXT_DAY returns the date of the first weekday named by char that is later than the date date.
The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument date.

next_day()的语法格式：
next_day := next_day(date,day_of_the_week);
它用来返回从第一个参数指定的日期开始，第一次到达第二个参数（星期）的日期.
参数day_of_the_week可以是从1到7的数字，也可以是sunday...saturday之类的英语单词(中文环境下是星期一。。星期日)

用途:用next_day得出本周的周一到周日的日期
取本周一是哪一天,有如下两种写法。

SELECT trunc(next_day(sysdate-7,2)) from dual;
--也可以使用
--SELECT trunc(next_day(sysdate-7,'星期一')) from dual;
也可以使用

The starting day of the week used by the format models DAY, DY, and D is specified implicitly by the initialization parameter NLS_TERRITORY.

注意:假如直接按照上述官方提供的文档，在如下的查询中可能会遇到问题，例如:

 

SELECT NEXT_DAY(SYSDATE, 'MONDAY ') FROM DUAL;

 

会提示如下的错误信息:

可以使用如下的方式解决:(查询下一个星期一是什么时间)

(第二个参数为星期一到星期六、星期日，当前第二个参数也可以是第2个参数可以是数字1-7，
分别表示周日到周六 )

SELECT NEXT_DAY(SYSDATE, '星期一 '),
       NEXT_DAY(SYSDATE, 2),
       SYSDATE 系统时间 
FROM DUAL;

CASE：查选下个周六是什么时间

  SELECT NEXT_DAY(SYSDATE, 7),
         SYSDATE 当前时间 
  FROM DUAL;

这时候，可以得到正确的结果:

TRUNC()函数的用法(不会进行四四舍五入：例如18.9，只取到18)

SELECT NEXT_DAY(SYSDATE, 'MONDAY ') FROM DUAL;

(注意国际通用的日期中；周末才是一个周的第一天，所以，当上面的参数为1的时候，表示查询的下一个周天是什么日期)

针对上面的错误信息，也可以进行系统参数的修改，其实上一个语句是对的，只是当前操作系统的参数不是美国，是中国，使用如下，修改参数：

alter   session   set   NLS_DATE_LANGUAGE   =   American; 
(修改该参数只影响当前的会话窗口)

-------------------------------------------------------------------------------------------------------------

TRUNC()函数的用法(不会进行四四舍五入：例如18.9，只取到18)
 首先他可以截取数字:

TRUNC（n,m);（m可以省略）
Purpose
The TRUNC (number) function returns n truncated to m decimal places. If m is omitted, then n is truncated to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point.

Examples
The following example truncate numbers:

(m为正整数的时候，表示截取有效位数，1表示从小数点开始向右边第一位开始截取，
-1表示从小数点开始向左边第一位开始截取)

SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;

Truncate
----------
15.7

SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL;

Truncate
----------
10

SELECT TRUNC(15.79,-2) "Truncate" FROM DUAL;

Truncate
----------
0

SELECT TRUNC(151.79,-1) "Truncate" FROM DUAL;

Truncate
----------
150

SELECT TRUNC(151.79,-2) "Truncate" FROM DUAL;

Truncate
----------
100

其次，他可以进行日期的截取
The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. If you omit fmt, then date is truncated to the nearest day.

SELECT TRUNC(SYSDATE, 'year') "New Year" FROM DUAL;

-------------------------------------------------------------------------------------------------------------

2、ROUND函数的用法:(会进行四舍五入)

SELECT ROUND(15.193,1) "Round" FROM DUAL;

Round
----------
15.2

The following example rounds a number one digit to the left of the decimal point:

SELECT ROUND(15.193,-1) "Round" FROM DUAL;

Round
----------
20

会进行四舍五入的：

SELECT SYSDATE 当前时间,
       ROUND(SYSDATE + 200, 'yy') 年,
       ROUND(SYSDATE + 6, 'mm') 月,
       ROUND(SYSDATE + 1 / 24, 'dd') 日,
       ROUND(SYSDATE-0.5 / 24, 'hh') 时,
       ROUND(SYSDATE, 'mi') 分
  FROM DUAL;

不会进行四舍五入的：

SELECT SYSDATE 当前时间,
       TRUNC(SYSDATE + 200, 'yy') 年,
       TRUNC(SYSDATE + 6, 'mm') 月,
       TRUNC(SYSDATE + 1 / 24, 'dd') 日,
       TRUNC(SYSDATE-0.5 / 24, 'hh') 时,
       TRUNC(SYSDATE, 'mi') 分
  FROM DUAL;

-------------------------------------------------------------------------------------------------------------

CEIL函数的使用
CEIL returns smallest integer greater than or equal to n.

Examples
The following example returns the smallest integer greater than or equal to 15.7:

SELECT CEIL(15.7) "Ceiling" FROM DUAL;

Ceiling
----------
16

SELECT CEIL(15.1) "Ceiling" FROM DUAL;

Ceiling
----------
16

-------------------------------------------------------------------------------------------------------------

SIGN函数的使用
IGN returns -1 if n<0, then . If n=0, then the function returns 0. If n>0, then SIGN returns 1.

 

Examples

SELECT SIGN(10), SIGN(-15), SIGN(0) FROM DUAL;