Chapter 04-Using Conversion Functions and Conditional Expressions-Conversion Functions
Conversions Functions
Implicit Data Type Conversion
In expressions,the Oracle server can automatically conver the following:
| From | To |
| VARCHAR2 or CHAR | NUMBER |
| VARCHAR2 or CHAR | DATE |
Demo-01:
View Code
SQL> SELECT employee_id,first_name,TO_CHAR(hire_date,'YYYY-MM-DD HH24:MI:SS') FROM employees WHERE hire_date > '01-JAN-90'; EMPLOYEE_ID FIRST_NAME TO_CHAR(HIRE_DATE,' ----------- -------------------- ------------------- 198 Donald 2007-06-21 00:00:00 199 Douglas 2008-01-13 00:00:00 200 Jennifer 2003-09-17 00:00:00 201 Michael 2004-02-17 00:00:00 202 arcerzhang 2005-08-17 00:00:00 203 Susan 2002-06-07 00:00:00 204 Hermann 2002-06-07 00:00:00 205 Shelley 2002-06-07 00:00:00 206 William 2002-06-07 00:00:00 100 Steven 2003-06-17 00:00:00 101 Neena 2005-09-21 00:00:00 EMPLOYEE_ID FIRST_NAME TO_CHAR(HIRE_DATE,' ----------- -------------------- ------------------- 102 Lex 2001-01-13 00:00:00 103 Alexander 2006-01-03 00:00:00 104 Bruce 2007-05-21 00:00:00 105 David 2005-06-25 00:00:00 106 Valli 2006-02-05 00:00:00 107 Diana 2007-02-07 00:00:00 108 Nancy 2002-08-17 00:00:00 109 Daniel 2002-08-16 00:00:00 110 John 2005-09-28 00:00:00 111 Ismael 2005-09-30 00:00:00 112 Jose Manuel 2006-03-07 00:00:00 EMPLOYEE_ID FIRST_NAME TO_CHAR(HIRE_DATE,' ----------- -------------------- ------------------- 113 Luis 2007-12-07 00:00:00 114 Den 2002-12-07 00:00:00 115 Alexander 2003-05-18 00:00:00 116 Shelli 2005-12-24 00:00:00 117 Sigal 2005-07-24 00:00:00 118 Guy 2006-11-15 00:00:00 119 Karen 2007-08-10 00:00:00 120 Matthew 2004-07-18 00:00:00 121 Adam 2005-04-10 00:00:00 122 Payam 2003-05-01 00:00:00 123 Shanta 2005-10-10 00:00:00 EMPLOYEE_ID FIRST_NAME TO_CHAR(HIRE_DATE,' ----------- -------------------- ------------------- 124 Kevin 2007-11-16 00:00:00 125 Julia 2005-07-16 00:00:00 126 Irene 2006-09-28 00:00:00 127 James 2007-01-14 00:00:00 128 Steven 2008-03-08 00:00:00 129 Laura 2005-08-20 00:00:00 130 Mozhe 2005-10-30 00:00:00 131 James 2005-02-16 00:00:00 132 TJ 2007-04-10 00:00:00 133 Jason 2004-06-14 00:00:00 134 Michael 2006-08-26 00:00:00 EMPLOYEE_ID FIRST_NAME TO_CHAR(HIRE_DATE,' ----------- -------------------- ------------------- 135 Ki 2007-12-12 00:00:00 136 Hazel 2008-02-06 00:00:00 137 Renske 2003-07-14 00:00:00 138 Stephen 2005-10-26 00:00:00 139 John 2006-02-12 00:00:00 140 Joshua 2006-04-06 00:00:00 141 Trenna 2003-10-17 00:00:00 142 Curtis 2005-01-29 00:00:00 143 Randall 2006-03-15 00:00:00 144 Peter 2006-07-09 00:00:00 145 John 2004-10-01 00:00:00 EMPLOYEE_ID FIRST_NAME TO_CHAR(HIRE_DATE,' ----------- -------------------- ------------------- 146 Karen 2005-01-05 00:00:00 147 Alberto 2005-03-10 00:00:00 148 Gerald 2007-10-15 00:00:00 149 Eleni 2008-01-29 00:00:00 150 Peter 2005-01-30 00:00:00 151 David 2005-03-24 00:00:00 152 Peter 2005-08-20 00:00:00 153 Christopher 2006-03-30 00:00:00 154 Nanette 2006-12-09 00:00:00 155 Oliver 2007-11-23 00:00:00 156 Janette 2004-01-30 00:00:00 EMPLOYEE_ID FIRST_NAME TO_CHAR(HIRE_DATE,' ----------- -------------------- ------------------- 157 Patrick 2004-03-04 00:00:00 158 Allan 2004-08-01 00:00:00 159 Lindsey 2005-03-10 00:00:00 160 Louise 2005-12-15 00:00:00 161 Sarath 2006-11-03 00:00:00 162 Clara 2005-11-11 00:00:00 163 Danielle 2007-03-19 00:00:00 164 Mattea 2008-01-24 00:00:00 165 David 2008-02-23 00:00:00 166 Sundar 2008-03-24 00:00:00 167 Amit 2008-04-21 00:00:00 EMPLOYEE_ID FIRST_NAME TO_CHAR(HIRE_DATE,' ----------- -------------------- ------------------- 168 Lisa 2005-03-11 00:00:00 169 Harrison 2006-03-23 00:00:00 170 Tayler 2006-01-24 00:00:00 171 William 2007-02-23 00:00:00 172 Elizabeth 2007-03-24 00:00:00 173 Sundita 2008-04-21 00:00:00 174 Ellen 2004-05-11 00:00:00 175 Alyssa 2005-03-19 00:00:00 176 Jonathon 2006-03-24 00:00:00 177 Jack 2006-04-23 00:00:00 178 Kimberely 2007-05-24 00:00:00 EMPLOYEE_ID FIRST_NAME TO_CHAR(HIRE_DATE,' ----------- -------------------- ------------------- 179 Charles 2008-01-04 00:00:00 180 Winston 2006-01-24 00:00:00 181 Jean 2006-02-23 00:00:00 182 Martha 2007-06-21 00:00:00 183 Girard 2008-02-03 00:00:00 184 Nandita 2004-01-27 00:00:00 185 Alexis 2005-02-20 00:00:00 186 Julia 2006-06-24 00:00:00 187 Anthony 2007-02-07 00:00:00 188 Kelly 2005-06-14 00:00:00 189 Jennifer 2005-08-13 00:00:00 EMPLOYEE_ID FIRST_NAME TO_CHAR(HIRE_DATE,' ----------- -------------------- ------------------- 190 Timothy 2006-07-11 00:00:00 191 Randall 2007-12-19 00:00:00 192 Sarah 2004-02-04 00:00:00 193 Britney 2005-03-03 00:00:00 194 Samuel 2006-07-01 00:00:00 195 Vance 2007-03-17 00:00:00 196 Alana 2006-04-24 00:00:00 197 Kevin 2006-05-23 00:00:00 107 rows selected.
Demo-02:
View Code
SQL> SELECT '923.14' + 29.06 FROM DUAL; '923.14'+29.06 -------------- 952.2
Explicit Data Type Conversion
Using the TO_CHAR Function with Dates
TO_CHAR(date,'format_model')
The format model:
- Must be enclosed with single quotation marks
- Is case-sensitive
- Can include any valid date format element
- Has an fm element to remove padded blanks or suppress leading zeros
- Is separated from the date value by a comma
Demo-01:no fm
View Code
SQL> SELECT employee_id,TO_CHAR(hire_date,'YYYY-MM-DD') Month_Hired FROM employees WHERE last_name = 'Higgins'; EMPLOYEE_ID MONTH_HIRE ----------- ---------- 205 2002-06-07
Demo-02:with fm
View Code
SQL> SELECT employee_id,TO_CHAR(hire_date,'fmYYYY-MM-DD') Month_Hired FROM employees WHERE last_name = 'Higgins'; EMPLOYEE_ID MONTH_HIRE ----------- ---------- 205 2002-6-7
Elements of the Date Format Model
| Element | Result |
| YYYY | Full year in numbers |
| YEAR | Year spelled out (in English) |
| MM | Two-digit value for the month |
| MONTH | Full name of month |
| MON | Three-letter abbreviation of the month |
| DY | Three-letter abbreviation of the day of the week |
| DAY | Full name of the day of the week |
| DD | Numeric day of the month |
Elements of the Date Format Model
Time elements format the time portion of the date: HH24:MI:SS AM 15:45:32 PM Add character strings by enclosing them with double quotation marks: DD "of" MONTH 12 of OCTOBER Number suffixes spell out numbers: ddspth fourteenth
Demo-01:
View Code
SQL> SELECT last_name,TO_CHAR(hire_date,'fmDD Month YYYY') AS HIREDATE FROM employees; LAST_NAME HIREDATE ------------------------- -------------------------------------------- OConnell 21 June 2007 Grant 13 January 2008 Whalen 17 September 2003 Hartstein 17 February 2004 Fay 17 August 2005 Mavris 7 June 2002 Baer 7 June 2002 Higgins 7 June 2002 Gietz 7 June 2002 King 17 June 2003 Kochhar 21 September 2005 LAST_NAME HIREDATE ------------------------- -------------------------------------------- De Haan 13 January 2001 Hunold 3 January 2006 Ernst 21 May 2007 Austin 25 June 2005 Pataballa 5 February 2006 Lorentz 7 February 2007 Greenberg 17 August 2002 Faviet 16 August 2002 Chen 28 September 2005 Sciarra 30 September 2005 Urman 7 March 2006 LAST_NAME HIREDATE ------------------------- -------------------------------------------- Popp 7 December 2007 Raphaely 7 December 2002 Khoo 18 May 2003 Baida 24 December 2005 Tobias 24 July 2005 Himuro 15 November 2006 Colmenares 10 August 2007 Weiss 18 July 2004 Fripp 10 April 2005 Kaufling 1 May 2003 Vollman 10 October 2005 LAST_NAME HIREDATE ------------------------- -------------------------------------------- Mourgos 16 November 2007 Nayer 16 July 2005 Mikkilineni 28 September 2006 Landry 14 January 2007 Markle 8 March 2008 Bissot 20 August 2005 Atkinson 30 October 2005 Marlow 16 February 2005 Olson 10 April 2007 Mallin 14 June 2004 Rogers 26 August 2006 LAST_NAME HIREDATE ------------------------- -------------------------------------------- Gee 12 December 2007 Philtanker 6 February 2008 Ladwig 14 July 2003 Stiles 26 October 2005 Seo 12 February 2006 Patel 6 April 2006 Rajs 17 October 2003 Davies 29 January 2005 Matos 15 March 2006 Vargas 9 July 2006 Russell 1 October 2004 LAST_NAME HIREDATE ------------------------- -------------------------------------------- Partners 5 January 2005 Errazuriz 10 March 2005 Cambrault 15 October 2007 Zlotkey 29 January 2008 Tucker 30 January 2005 Bernstein 24 March 2005 Hall 20 August 2005 Olsen 30 March 2006 Cambrault 9 December 2006 Tuvault 23 November 2007 King 30 January 2004 LAST_NAME HIREDATE ------------------------- -------------------------------------------- Sully 4 March 2004 McEwen 1 August 2004 Smith 10 March 2005 Doran 15 December 2005 Sewall 3 November 2006 Vishney 11 November 2005 Greene 19 March 2007 Marvins 24 January 2008 Lee 23 February 2008 Ande 24 March 2008 Banda 21 April 2008 LAST_NAME HIREDATE ------------------------- -------------------------------------------- Ozer 11 March 2005 Bloom 23 March 2006 Fox 24 January 2006 Smith 23 February 2007 Bates 24 March 2007 Kumar 21 April 2008 Abel 11 May 2004 Hutton 19 March 2005 Taylor 24 March 2006 Livingston 23 April 2006 Grant 24 May 2007 LAST_NAME HIREDATE ------------------------- -------------------------------------------- Johnson 4 January 2008 Taylor 24 January 2006 Fleaur 23 February 2006 Sullivan 21 June 2007 Geoni 3 February 2008 Sarchand 27 January 2004 Bull 20 February 2005 Dellinger 24 June 2006 Cabrio 7 February 2007 Chung 14 June 2005 Dilly 13 August 2005 LAST_NAME HIREDATE ------------------------- -------------------------------------------- Gates 11 July 2006 Perkins 19 December 2007 Bell 4 February 2004 Everett 3 March 2005 McCain 1 July 2006 Jones 17 March 2007 Walsh 24 April 2006 Feeney 23 May 2006 107 rows selected.
Using the TO_CHAR Functions with Numbers
TO_CHAR(number,'format_model')
These are some of the format elements that you can use with the TO_CHAR function to display a number value as a character:
| Element | Result |
| 9 | Represents a number |
| 0 | Forces a zero to be displayed |
| $ | Places a floating dollar sign |
| L | Uses the floating local currency symbol |
| . | Prints a decimal point |
| , | Prints a comma as a thousands indicator |
Demo-01:
View Code
SQL> SELECT TO_CHAR(salary,'$99,999.00') SALARY FROM employees WHERE last_name = 'Ernst'; SALARY ----------- $6,000.00
Demo-02:
View Code
SQL> SELECT TO_CHAR(123456789.996,'$999,999,999.00') AS MONEY FROM DUAL; MONEY ---------------- $123,456,790.00 SQL> SELECT TO_CHAR(123456789.996,'$999,999,999,999.00') AS MONEY FROM DUAL; MONEY -------------------- $123,456,790.00
Using the TO_NUMBER and TO_DATE Functions
Convert a character string to a number format using the TO_NUMBER function:
TO_NUMBER(char[,'format_model'])
Convert a character string to a date format using the TO_DATE function:
TO-DATE(char[,'format_model'])
These functions have fx modifier.This modifier specifies the exact match for the character argument and date format model of a TO_DATE function.
Demo-01:TO_NUMBER
View Code
SQL> SELECT TO_NUMBER('-$12,345.67','99,999.99') FROM dual; SELECT TO_NUMBER('-$12,345.67','99,999.99') FROM dual * ERROR at line 1: ORA-01722: invalid number SQL> SELECT TO_NUMBER('-$12,345.67') FROM dual; SELECT TO_NUMBER('-$12,345.67') FROM dual * ERROR at line 1: ORA-01722: invalid number SQL> SELECT TO_NUMBER('-$12,345.67','$99,999.99') FROM dual; TO_NUMBER('-$12,345.67','$99,999.99') ------------------------------------- -12345.67
Demo-02:TO_DATE
View Code
SQL> SELECT TO_DATE('July 4,2007','Month DD,YYYY') FROM DUAL; TO_DATE(' --------- 04-JUL-07 SQL> SELECT TO_DATE('July 4, 2007 ','Month DD,YYYY') FROM DUAL; TO_DATE(' --------- 04-JUL-07 SQL> SELECT TO_DATE('July 4, 2007 ','fxMonth DD,YYYY') FROM DUAL; SELECT TO_DATE('July 4, 2007 ','fxMonth DD,YYYY') FROM DUAL * ERROR at line 1: ORA-01858: a non-numeric character was found where a numeric was expected
Using the TO_CHAR and TO_DATE Functions with RR Data Format
To find employees hired before 1990,use the RR date format,which produces the same results whether the command is run in 1999 or now:
View Code
SQL> SELECT last_name,TO_CHAR(hire_date,'DD-Mon-YYYY') FROM employees WHERE hire_date < TO_DATE('01-Jan-90','DD-Mon-RR'); no rows selected SQL>
