One , Date function
The date function is used to process date Type of data , Subtract two dates by the number of days between the return dates . Addition is not allowed for dates , meaningless .
Common representative symbols ：yyyy year ,mm month ,dd day ,hh hour ,mi minute ,ss second ,day week
By default, the date format is dd-mon-yy Namely 12-3 month -19
（1）sysdate: This function returns the system time
（2）months_between(m,n) date m And date n How many months is the difference
（3）add_months(d,n) On the date d On the increase n Months
（4）next_day(d, ‘ week *’) Specify the date d Next week * Corresponding date
（5）last_day(d)： Returns the specified date d Last day of month
（6）extract(month from d) From date d Number of months to extract
（7）round(d,time) Rounding of dates
（8）trunc(d,time) Date truncation
Here are some examples and renderings of date functions ：
Example results of various cases
months_betweenselect months_between(‘01-9 month -95’,‘11-1 month -94’) from dual;
19.6774193548387
add_monthsselect add_months(‘11-2 month -18’,6) from dual;2018/8/11
next_dayselect next_day(‘11-2 month -18’,‘ Saturday ’) from dual;2018/2/17
last_dayselect last_day(‘11-2 month -18’) from dual;2018/2/28
round Round off months 25-7 month -18select round(to_date(‘25-7 month -2018’),‘month’) from dual;
2018/8/1
round Rounding year 25-7 month -18select round(to_date(‘25-7 month -2018’),‘year’) from dual;
2019/1/1
trunc Truncate month 25-7 month -18select trunc(to_date(‘25-7 month -2018’),‘month’) from dual;
2018/7/1
trunc Truncated year 25-7 month -18select trunc(to_date(‘25-7 month -2018’),‘year’) from dual;
2018/1/1
eg： Find employed 8 More than months employees
SQL>
select * from emp where sysdate>=add_months(hiredate,8);
eg： Display full 10 Name and date of employment of the employee with years of service .
SQL>
select ename, hiredate from emp where sysdate>=add_months(hiredate,12*10);
eg： For each employee , Displays the number of days it has joined the company .
SQL> select floor(sysdate-hiredate),ename from emp;
perhaps
SQL> select trunc(sysdate-hiredate),ename from emp;
eg： Find the bottom of each month 3 All employees employed per day .
SQL>
select hiredate,ename from emp where last_day(hiredate)-2=hiredate;
Two , Conversion function
Conversion functions are used to convert data types from one to another . In some cases ,oracle server The data type of the allowed value is different from the actual value , At this time oracle
server The data type will be converted implicitly
What we're going to say is that although oracle You can convert implicit data types , But it doesn't fit all situations , In order to improve the reliability of the program , We should use the conversion function for conversion .
（1）to_char function
format : to_char(date,‘format’)
1, Must be in single quotation marks and case sensitive .
2, Any valid date format can be included .
3, The dates are separated by commas .
eg： Can date be displayed Time / branch / second
SQL> select ename, to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
eg： Can salary display the specified currency symbol
SQL> select sal,to_char(sal,'$999,999.99') from emp;
yy： Two digit year 2004–>04
yyyy： Four digit year 2004 year
mm： Two digit month 8 month –>08
dd： Two digit day 30 Number –>30
hh24： 8 spot –>20
hh12：8 spot –>08
mi,ss–> Show minutes / second
9： Show numbers , And ignore the front 0
0： Show numbers , If the number of digits is insufficient , Then use 0 Make up
.：（ decimal point ） Displays the decimal point at the specified position
,：（ Thousand character ） Displays a comma at the specified location
$：（ Dollar sign ） Add dollars before the numbers
L：（ Local currency symbol ） Prefix the number with the local currency symbol
C：（ International currency symbol ） Prefix the number with the international currency symbol
eg： When the salary is displayed , Put the local currency unit first
SQL> select ename, to_char(sal,'L99999.99')from emp;
eg： display 1980 All employees in
SQL> select * from emp where to_char(hiredate, 'yyyy')=1980;
eg： Show all 12 Monthly employees
SQL> select * from emp where to_char(hiredate, 'mm')=12;
there 12 and 1980 It's numbers , Can be added ’ ’ Or not , because Oracle Will switch automatically , But it's better to add .
eg： Show name ,hiredate What day is the working day with the employee
SQL> select ename,hiredate,to_char(hiredate,'day') from emp;
（2）to_date function
format ：to_date(string,‘format’)
function to_date Used to convert strings to date Type of data .
eg： Put the string 2015-03-18 13:13:13 Convert to date format ,
SQL> select to_date('2015-03-18 13:13:13','yyyy-mm-dd hh24:mi:ss') from dual;
（3）to_number function
format ：to_number(char,‘format’)
use to_number Function to convert a character to a date .
SQL> select to_number('￥1,234,567,890.00','L999,999,999,999.99') from dual;
Technology