Oracle Series: (9) Three major types of conversions

Three major types and implicit data type conversions in Oracle

(1) varchar2 variable length/char fixed length-->number, for example: ' 123 '->123

(2) Varchar2/char-->date, for example: ' 2 May-April -15 ', ' 2 May-April-15 '

(3) Number---->varchar2/char, e.g.:123-> ' 123 '

(4) Date------>varchar2/char, for example: ' 2 May-April -15 ', ' 2 May-April-15 '

number<----->varchar2/char<----->date

How Oracle implicitly TRANSFORMS:

1) = The type of the two sides of the = number is the same

2) If the = two sides of the type are different, try to do the conversion

3) in the conversion, to ensure that legitimate, or the conversion will fail, for example: December will not have 32 days, the year will not have 1 March

0. Stealth Conversion

Enquiry for December 17, 1980 Employees (mode one: Date implicit conversion)

SELECT * from emp where hiredate = ' 1 July-December-80 ';

1. Steering string

1.1. Turn from date to string

Convert dates to strings using To_char (date, ' grid ' constant ') ' function

The following format is displayed: Saturday, April 25, 2015

Select To_char (sysdate, ' yyyy ' year "MM" month "DD" Day "Days") from dual;

Use the To_char (date, ' format ') function to turn the date into a string , as shown in format: 2015-04-25 today is Saturday 15:15:15

Select To_char (sysdate, ' yyyy-mm-dd ' Today is "Day Hh24:mi:ss") from dual;

Or

Select To_char (sysdate, ' yyyy-mm-dd ' Today is "Day HH12:MI:SS AM") from dual;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/86/F7/wKiom1fP2Dyj8TDdAAAwi4fBv0w391.png "title=" 001. PNG "alt=" Wkiom1fp2dyj8tddaaawi4fbv0w391.png "/>

1.2. Turn the value to string

Use the To_char (value, ' format ') function to convert a value to a string that displays the following format: $1,234

Select To_char (1234, ' $9,999 ') from dual;

Use the To_char (value, ' format ') function to convert a value to a string that displays the following format: ￥1,234

Select To_char (1234, ' $9,999 ') from Dual;select to_char (1234, ' l9,999 ') from dual;

Note:L represents the meaning of locale

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/86/F7/wKiom1fP2NGxCkZHAAATQl5O1xo704.png "title=" 002. PNG "alt=" Wkiom1fp2ngxckzhaaatql5o1xo704.png "/>

2. Turn the date by the string

Use the To_date (' string ', ' format ') function to query the December 17, 1980 entry staff (mode two: date explicit conversion)

SELECT * from emp where hiredate = To_date (' December 17, 1980 ', ' yyyy ' year "MM" month "DD" Day ");

Or

SELECT * from emp where hiredate = to_date (' 1980#12#17 ', ' yyyy "#" MM "#" DD ');

Or

SELECT * from emp where hiredate = to_date (' 1980-12-17 ', ' yyyy-mm-dd ');

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/86/F8/wKiom1fP2X-RXnbsAAA0tpFAiuI192.png "title=" 003. PNG "alt=" Wkiom1fp2x-rxnbsaaa0tpfaiui192.png "/>

3. Turn the value from the string

Use the To_number (' string ') function to turn the string ' 123 ' into a number 123

Select To_number (' 123 ') from dual;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/86/F6/wKioL1fP2kCAHG6uAAAJ2OFGgls795.png "title=" 004. PNG "alt=" Wkiol1fp2kcahg6uaaaj2ofggls795.png "/>

Attention:

Select ' 123 ' + 123 from dual;

Results: 246

Select ' 123 ' | | 123 from Dual;

Results: 123123

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/86/F6/wKioL1fP2pjAQ_bRAAAQL5RcqyE861.png "title=" 005. PNG "alt=" Wkiol1fp2pjaq_braaaql5rcqye861.png "/>

Oracle Series: (9) Three major types of conversions