Oracle Functions-Single-line function-number, date, date processing function

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/8C/13/wKioL1hhJnvjwkHvAABh8bZO-Vc778.png-wh_500x0-wm_3 -wmp_4-s_2715184897.png "title=" 1.png "alt=" Wkiol1hhjnvjwkhvaabh8bzo-vc778.png-wh_50 "/>

Classification of functions

Single-line function: One input corresponds to a output,input and output one by one corresponding relationships such as lower

Group function: multiple input, but only one output. such as SUM ()

==============================================================

Single-line function

Characteristics:

Each row returns one result, and the input output has a one by one corresponding relationship

can be nested use, the output of one function can do the input of another function such as: SELECT Lowner (Upper (' AA ')) from dual;

The passed-in variable can be either a column value or an expression. such as select lower (ename) from EMP;

==============================================================

1. Numeric functions

Working with numbers is a complement to subtraction

Sql>select round (45.926,2) from dual; --Rounding, second digit reserved several decimal points

Sql>select trunc (45.926,2) from dual; --direct interception, no rounding

Sql>select MoD (10,3) from dual; --Seeking remainder

2. Date function

The default display format for Oracle database storage dates is DD-MON-YY

Modify date display format:

Alter session set nls_date_format= ' < required format > ';

3. SYSDATA: Check the system time

Sql>select sysdate from dual; --View the current time of the system

Sql>select sysdate,sysdate-7 from dual; -Minus 1 weeks

Sql>select sysdate,sysdate-1 from dual; -Minus 1 days

Sql>select sysdate,sysdate-1/24 from dual; -Minus 1 hours

Sql>select sysdate,sysdate-1/24/60 from dual; -Minus 1 minutes

Sql>select sysdate,sysdate-1/24/60/60 from dual; -Minus 1 seconds

4. Date processing function

An existing date plus or minus a value will get a date. such as select sysdate,sysdate-7 from dual;

Two date subtraction can get two days of the day (time interval) such as SELECT (Sysdate-hiredate)/7 weeks from EMP;

①months_between number of months between two dates

Sql>select Months_between (sysdate,sysdate-1000) from dual;

②add_months How many months to add to a specified date

Sql>select add_months (sysdate,2) from dual;

③last_date the last day of the specified date

Sql>select Last_day (sysdate) from dual;

5, the date of rounding:

Rounding for the year, according to January-June, July-December

Rounding for month, 1-15, 15-31 days

On the day, according to Sunday to Wednesday, note: The beginning of the week is Sunday.

Sql> select Sysdate from dual;

Sysdate

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

16-may-16 is currently May 16, 16

==============================================================

Sql> Select round (sysdate, ' year ') from dual;

----Rounding up the year, which is accurate to the year, now May, not June, so the result is 16-1-1

ROUND (Sysdate)

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

01-jan-16

==============================================================

Sql> Select round (sysdate+60, ' year ') from dual; -May 16 plus 60 days, it's July, so the result is 17-1-1

ROUND (Sysdate)

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

01-jan-17

==============================================================

Sql> Select round (sysdate, ' month ') from dual;

The-----rounds the month, which is the exact month. It's number 16th, over 15. All results for the next month 1st

ROUND (Sysdate)

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

01-jun-16

==============================================================

Sql> Select round (sysdate+22, ' month ') from dual;

----is now May 16, 22 days after July 7, 7th No 15, so the result is 16-07-01

ROUND (Sysdate)

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

01-jul-16

==============================================================

Sql>!date

Mon May 22:47:25 CST 2016

May 16, 2016, Monday, the starting time of this week is May 15 (one weeks for every Sunday, Saturday for one weeks)

==============================================================

Sql>select round (sysdate, "Day") from dual;

----to round the day, that is, the days of the week May 16 is Monday, not in Wednesday, so do not "carry", showing the first day of the week

ROUND (Sysdate)

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

15-may-16

==============================================================

Sql>select round (sysdate+3, "Day") from dual;

----16th is Monday, plus three days is Thursday, more than Wednesday, so carry, show next week's Monday

==============================================================

Sql>select round (sysdate-3, "Day") from dual;

----Monday minus three days is the last week of Friday, more than Wednesday, so carry, show the current week of the next Monday, that is, number 15th

This article is from "Rookie Talent" blog, please be sure to keep this source http://omphy.blog.51cto.com/7921842/1886342

Oracle Functions-Single-line function-number, date, date processing function