Date type of the database

Establish an Instance data table to create an order form myorder1111

Table Sub-segment:

ID  number Primary  key (value cannot be heavy, cannot be empty) name  varchar2 (in) Money  numberodate  Date

Inserting data into a table

1, ' jd0001 ', 123.45, sysdate

2, ' jd0002 ', 345.85, sysdate

Submit data (Commit)

　　

Change the order amount with ID equal to 2 to 2345.68.

Date type Introduction (1) The default representation of a date type

' Dd-mon-yy '

(2) How to change the default performance

To_char (PAR1, par2) par1   Date data to process or date field par2   is a date format

Date format:

• yyyy 4-year
• mm 2-bit month
• DD 2-bit days
• hh 12-hour system
• hh24 24-hour system
• mi min
• SS sec

• Day of the Week
• The abbreviation of Mon month
• Full write for month months

Demo: Sort by entry date, shows_emp table in ID salary start_date 。

Select ID, salary, start_date from S_emp order by start_date;

。。。。。。

You can avoid some misunderstandings by using the TO_CHAR format:

Select ID, salary, to_char (start_date, ' yyyy-mm-dd hh24:mi:ss ') start_date from S_emp order by start_date;

。。。。。。

View S_emp's script can only id=1 time is to_date put in month day seconds, the other is the default put date (08-mar-90), the default is 0 seconds.

Put any point in time into the database

To_date (PAR1,PAR2)/* Change Date string to date */

• Par1 date string ' 2008-08-08 20:08:08 '
• PAR2 date format string, same as To_char

Demonstrate:

INSERT into Myorder values (' bj00001 ', 200000,to_date (' 2008-08-08 20:08:08 ', ' yyyy-mm-dd hh24:mi:ss ')), select ID, To_char (odate, ' yyyy-mm-dd hh24:mi:ss ') odate from Myorder where id=2008;

INSERT into Myorder values (' Chuan_piao ', 888888,to_date (' 2012-12-22 23:59:59 ', ' yyyy-mm-dd hh24:mi:ss '), select ID, To_char (odate, ' yyy-mm=dd hh24:mo:ss ');

Adjustment of dates

Performance Current system date:

Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

Adjust Day: plus 1

Select To_char (sysdate+1, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

Adjustment one hours: plus 1/24

Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), To_char (sysdate+1/24, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

Adjust by the minute in seconds and so on:

Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), To_char (SYSDATE+1/24 (24*60*60), ' Yyyy-mm-dd Hh24:mi:ss ') from dual;

Special adjustments

Add_months (PAR1,PAR2)     adjusted per month

Demonstrate:

Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), To_char (Add_mouths (sysdate, 1), ' Yyyy-mm-dd Hh24:mi:ss ') from dual;

　　

Round (PAR1,PAR2) (less)

• Par1 the date or Date field to process
• PAR2 the unit to be rounded is the default unit is day (half a day or more into, half a day below)

' HH ' is the hour unit (more than half an hour to enter, half an hour below)

' mm ' month, ' yy ' year

Demo:

In days:

Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), To_char (Round (sysdate), ' Yyyy-mm-dd Hh24:mi:ss ') from dual;

In Hours:

Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), To_char (Round (sysdate), ' hh ', ' yyyy-mm-dd hh24:mi:ss ') from dual;

Trunc (PAR1,PAR2) (less)

• Par1 the date or Date field to process
• PAR2 units to intercept the default unit is the day (half a day or more into, half a day below)

Usage and round, just not in, no matter how much behind is directly cut off

Demo: Intercept by Month: (date not 0, date changed to 1 after interception)

Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), To_char (Trunc (sysdate, ' mm '), ' Yyyy-mm-dd Hh24:mi:ss ') from dual;

Comprehensive processing of dates

Sysdate   Gets the last second of the last day of the month corresponding to this date

August should be received: 2014-08-31 23:59:59

' 2008-01-20 08:30:25 ' should get: 2014-01-31 23:59:59

' Yyyy-mm-dd Hh24:mi:ss ') from dual;

Last_day (PAR1) Gets the last day of the month corresponding to the date corresponding to the point in time

Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), To_char (Trunc (Last_day)) sysdate (+1-1/), ' 24*60*60 yyyy-mm-dd: Mi:ss ') from dual;

Next_day (PAR1,PAR2) A few hours next week.

08-25 is Monday, the next one weeks one is 09-01

Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), To_char (Mext_day (sysdate, ' moneday '), ' Yyyy-mm-dd hh24:mi:ss ') from Dual

08-25 is Monday, next Friday is 08-29 (closest to 08-25 Friday)

Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), To_char (Mext_day (sysdate, ' Friday '), ' Yyyy-mm-dd hh24:mi:ss ') from Dual

Date type of the database