Oracle data types look simple, but you will find many knowledge points when you use them. This article is the data type of ORACLE date compiled by me, which is all the introductory materials for development, and I would like to share it with you:

Note: Because interval and time zone are rarely used in practice, these two aspects are not discussed in this article.

1, universal date data type

1. 1, date

This is the most commonly used date type in ORACLE. It can save date and time and can be used for common date processing. The date range can be from 47 BC121to AD1nine thousand nine hundred and ninety-nine12 February 3 1. The storage of date type in the database is fixed at 7 bytes, and the format is:

Byte 1: Century+100

Byte 2: Year

Byte 3: Month

Byte 4: Day

Byte 5: hour+1

Byte 6: Minute+1

Byte 7: second+1

1.2, timestamp (p)

This is also a common date type in ORACLE. The difference between it and date is that it can save not only date and time, but also fractional seconds. The number of decimal places can be specified as 0-9, and the default is 6 digits, so the highest accuracy can reach ns (nanosecond), and it can be stored as 7 or 1 1 byte in the database. If the precision is 0, it can be stored in 7 bytes, which is the same as the function of date type. If the precision is greater than 0, it can be stored in 7 bytes.

The format is:

Byte 1: Century+100

Byte 2: Year

Byte 3: Month

Byte 4: Day

Byte 5: hour+1

Byte 6: Minute+1

Byte 7: second+1

8th-11byte: nanosecond, 4 bytes storage, internal operation type is plastic. Note: If you add or subtract values, the timestamp date type will be automatically converted to date type, which means that fractional seconds will be automatically deleted.

1.3, date and timestamp type internal storage verification

Copy code

1 create table t

2 (

3 C 1 date,

4 C2 timestamp (9)

5 );

six

7 is inserted into t(c 1, c2) value (date' 20 10-2- 12', time stamp' 2010-2-13:. 8 is inserted into the value of t(c 1, c2) (

9 to _ date(' 20 10-2- 12 10:20:30 '，' YYYY-MM-DD HH24:MI:SS ')， 10 to _ timestamp(' 20 10-2- 12 13:24:52. 123456 '，' YYYY-MM-DD HHFF6 ') 1 1)；

12

13 SQL & gt； select c 1，dump(c 1) c 1_d，c2，dump(C2)C2 _ d from t； Copy code

c 65438+ 0 c 1 _ D C2 C2 _ D-------20 10-2-65438+ 02 Typ = 12 Len = 7: 120， 10，2， 12， 1， 1， 1， 13 1 12-Fe b- 10 0 1 . 24 . 52 . 123456000PM Typ = 180 Len = 1 1: 120， 1 10，2， 12，655438

1 SQL & gt； select c2，dump(c2， 16)C2 _ d 16 from t； C2 C2 _ d 16--- 12-FEB- 10 0 1 . 24 . 52 . 234 1232 1 1PM Typ = 180 Len = 1 1 :78，6e，2，c，e， 19，330select to_number('0df46fcb '，' xxxxxxxx') mydata 1，to_number('075bca00 '，' xxxxxxxx ')mydata 2 from dual； MYDATA 1 MYDATA2

- -

234 1232 1 1 123456000

2. Frequently asked questions

2. 1, how to get the current time?

Sysdate-Returns the current system date and time, accurate to the second systimestamp-Returns the current system date and time, accurate to 2.2 milliseconds, and how to perform date calculation.

Date data can be added or subtracted to get a new date. The unit of addition and subtraction is day sysdate+ 1- take the current time of tomorrow.

Sydate- 1/24- Take one hour before the current time.

SQL> Select sysdate d 1, sysdate+ 1 d2, sysdate-1/24d3 from dual; d 1 D2 D3---20 10-5- 13: 10:55: 16:20 10-5- 14: 16:20 10-5- 13:09:55。

You can directly subtract two dates, and the unit returned is days. Hours, minutes and seconds will be converted to decimal SQL >；; Select the date' 2012-01-01'-sysdate from dual; Date' 2012-01-01'-system date

-

597.046030092593

2.4. How to date people?

to_char(sysdate，' YYYY-MM-DD HH24:MI:SS ')

2.5. How to convert characters into dates?

to _ date(' 20 10-02-24 15:0 1:54 '，' yyyy-mm-ddhh 24:mi:ss ')to _ timestamp(' 1999-65438)。

3. 1, to _ char (date, formatstr)- format the date as the character SQL >；; select to_char(sysdate，' YYYY-MM-DD HH24:MI:SS ')d 1 from dual； D 1

-

20 10-05- 13 22:56:38

Examples of other uses of TO_CHAR

Copy code

1 SQL & gt； Select to _ char (date' 2010-02-12',' d') week _ day,-day of the week (1-7), Sunday = 1, Monday =2, Tuesday. Saturday = 72 to _ char (date' 2010-02-12',' DD') month _ day,-What day in a month is 3 to _ char (date' 2010-02-/? -What day of the year is 4 to _ char (date' 2010-02-12',' day') working day name,-English week name 5 _ char (date' 20/kloc-0-02-1) -week (0-4) of a month 6 to _ char (date' 2010-02-12',' WW ')Year _ Week- week of a year.

Copy code

WEEK _ DAYTH MONTH _ DAYTH YEAR _ DAYTH weekday name MONTH _ WEEKTH YEAR _ WEEKTH-------6 12 043 FRIDAY 2 073.2，to _ date (char， Formatstr)- Converts characters to date to date ('2010-02-2415: 01:54',' yyyy-mm-.

HH stands for 12 hour, HH24 stands for 24 hours, MM stands for months, and MI stands for minutes.

3.3.trunc(DATE)- Returns the date part of the date with the time of 0: 0:00:00 SQL>；; Select sysdate d 1, trunc (sysdate) d2 from dual; D 1 D2

Afternoon -20 10-5- 13.

3.4.Extract (data from DATEvalue)-returns a part of the date. If DATEVALUE is a date type, the data can be (year, month, day). If DATEVALUE is timestamp type, the data can be (year, month, day, hour, minute, second) SQL & gtselect sysdate d 1, extract (year from sysdate) this year, extract (minute from systimestamp) thism from dual; D/kloc-0 this year/m-20 this year10-5-65448. 20 10 53.5, Add _ months (date, month) -Add _ months (system date, 3)- current date plus 3 months.

ADD_MONTHS is an interesting function, which automatically processes large and small months and leap months, as shown in the following figure:1SQL > Select ADD_MONTHS (date' 20 10-2- 12', 1), 2 ADD_MONTHS (date' 20 1 2-27',1),

3 ADD_MONTHS (date' 20 10-2-28', 1),

4 ADD_MONTHS (date' 20 10- 1-3 1', 1)

5 from dual

6 ;

ADD_MONTHS (date' 20 10-2- 12', 1) ADD_MONTHS (date' 2012-27', 1) ADD_MONTHS. 1) ADD_MONTHS (date' 201kloc-0/select LAST_DAY from dual (date' 2065438+00-2-12'); Last day (date "20 10-2- 12")

-

20 10-2-28

3.7.Next _ day (date, CHAR)- Returns the date of the week specified by the next character of the given date SQL >；; SELECT NEXT_DAY (date' 20 1 0-21','Monday') NEXTDAY 1, NEXT_DAY (date' 20 10-2-22',' Monday').

- -

20 10-2-22 20 10-3- 1

TO _ TO _ ym interval(CHAR)-)- returns the time interval in [year-month] format, which is generally used for date addition and subtraction by 3.8, and to _ to _ ds interval (char)-returns the time interval in [day hour: minute: second] format, which is generally used for date addition and subtraction by SQL >；; select date ' 20 10-2- 12 '+TO _ ym interval(' 0 1-02 ')new date from dual； New date

-

20 1 1-4- 12

3.9.Numtoyminterval (n, CHAR)- Returns the time interval value of the unit specified in CHAR, which is usually used for date addition and subtraction. Characters can be years or months.

1 SQL & gt； select date ' 20 10-2- 12 '+NUMTOYMINTERVAL( 1，' year') newdate 1，2 date ' 20 10-2- 12 '+NUMTOYMINTERVAL( 1，' month ')new date 23 from dual；

New date 1 new date 2

- -

20 1 1-2- 12 20 10-3- 12

3. 10, numtodsinterval (n, CHAR)- Returns the time interval value of the unit specified in CHAR, which is generally used for date addition and subtraction. Characters can be days, hours, minutes and seconds.

1 SQL & gt； select date ' 20 10-2- 12 '+NUMTODSINTERVAL( 1，' DAY') newdate 1，2 date ' 20 10-2- 12 '+NUMTODSINTERVAL( 1，' HOUR ')new date 23 from dual；