Oracle series: (9) three types of conversions

Three types and implicit data type conversion in oracle

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

(2) varchar2/char-- > date, for example:'25-April-15 years->'25-April-15'

(3) number---- > varchar2/char, for example: 123-> '123'

(4) date- > varchar2/char, for example:'25-April-15 years->'25-April-15'

Numbervarchar2/chardate

How oracle implicitly converts:

1) whether the types on both sides of the = sign are the same

2) if the two sides of the = sign are of different types, try to convert them.

3) make sure that the conversion is legal and reasonable, otherwise the conversion will fail. For example, there will be no 32 days in December and no 13 months in a year.

0. Hidden conversion

Inquire about the employees who joined the staff on December 17, 1980 (method 1: implicit date conversion)

Select * from emp where hiredate ='17-December-80'

1. Turn to string

1.1. Change from date to string

Convert a date to a string using the to_char (date, 'case' constant') function

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

Select to_char (sysdate,'yyyy "mm" month "dd" day') from dual

Use the to_char (date, 'format') function to convert 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 hh34:mi:ss') from dual

Or

Select to_char (sysdate,'yyyy-mm-dd "today is" day HH12:MI:SS AM') from dual

1.2. Change from numeric value to string

Use the to_char (numeric, format) function to convert a numeric value into a string, which is displayed in the following format: $1234

Select to_char (1234) from dual

Use the to_char (numeric, 'format') function to convert the numeric value into a string, which is displayed in the following format: ¥1234

Select to_char (1234) from dual;select to_char (1234) from dual

Note: l stands for Locale

2. Change from string to date

Use the to_date ('string', 'format') function to query employees who joined on December 17, 1980 (method 2: date explicit conversion)

Select * from emp where hiredate = to_date ('December 17, 1980', 'yyyy' mm month'dd')

Or

Select * from emp where hiredate = to_date ('1980, 12, 17, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 12, 17, 1980, 12, 17, 1980, 1980, 12, 17, 1989, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 12, 17, 1980, 1980, 12, 17

Or

Select * from emp where hiredate = to_date ('1980-12-17mm Maidd`)

3. Change from string to numeric value

Use the to_number ('string') function to convert the string '123' to the number 123

Select to_number ('123') from dual

Note:

Select '123' + 123 from dual

Results: 246

Select '123' | | 123 from dual

Results: 123123