Explicit Datatype Conversion
TO_NUMBER TO_DATE
NUMBER CHARACTER DATE
TO_CHAR TO_CHAR
TO_CHAR Function with Dates
TO_CHAR (date, ‘fmt’)
The format model:
• Must be enclosed in single quotation marks and is case
sensitive.
• Can include any valid date format element
Date Format Model Elements
For assignments, Oracle can automatically convert
YYYY Full year in numbers
YEAR Year spelled out
MM 2-digit value for month
M0NTH Full name of the month
DY 3-letter abbreviation of
the day of the week
DAY Full name of the day
Date Format Model Elements
• Time elements format the time portion of the date.
HH24:MI:SS AM 15:45:32 PM
• Add character strings by enclosing them in
double
quotation marks
DD “of” MONTH 12 of OCTOBER
• Number suffixes spell out numbers
ddspth fourteenth
Using TO_CHAR Function with Dates
SQL > SELECT ename,
2 TO_CHAR (hiredate, ‘fmDD Month YYYY’) HIREDATE
3 FROM emp;
ENAME HIREDATE
KING 17 November 1981
BLAKE 1 May 1981
CLARK 9 June 1981
JONES 2 April 1981
MARTIN 28 September 1981
ALLEN 20 February 1981
TO_CHAR Function with Numbers
TO_CHAR (number, ‘fmt’)
Use these formats with the TO_CHAR function to
display a number value as a character
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 thousand indicator
Using TO_CHAR Function with Numbers
SQL > SELECT TO_CHAR (sal, ‘$99, 999’) SALARY
2 FROM emp
3 WHERE ename = ‘SCOTT’;
SALARY
$ 3,000
TO_NUMBER and TO_DATE Functions
• Convert a character string to a number format
using the TO_NUMBER function
TO_NUMBER (char)
• Convert a character string to a date format
using the TO_DATE function
TO_DATE (char[, ‘fmt’])
NVL Function
Converts null to an actual value
• Datatype that can be used are date, character, and
number.
• Datatypes must match
– NVL (comm, 0)
– NVL (hiredate, ’01-JAN-97’)
– NVL (job, ‘No Job Yet’)
Using the NVL Function
SQL > SELECT ename, sal, comm, (sal*12) +NVL (comm,0)
2 FROM emp;
ENAME SAL COMM (SAL*12)+NVL(COMM,0)
KING 5000 60000
BLAKE 2850 34200
CLARK 2450 29400
JONES 2975 35700
MARTIN 1250 1400 16400
ALLEN 1600 300 19500
….
14 row selected
Decode Function
Facilitates conditional inquiries by doing the work of a
CASE or IF-THEN-ELSE statement
DECODE (col / expression, search1, result1
[, search2, result1, …….. ,]
[, default])
Using the Decode Function
SQL > SELECT job, sal,
2 DECODE (job, ‘ANALYST’ , SAL*1.1,
3 ‘CLERK’, SAL*1.15,
4 ‘MANAGER’, SAL*1.20,
5 SAL)
6 REVISED_SALARY
7 FROM emp;
JOB SAL REVISED _SALARY
PRESIDENT 5000 60000
MANAGER 2850 34200
MANAGER 2450 29400
….
14 row selected
Nesting Functions
• Single-row functions can be nested to any level.
• Nested functions are evaluated from deepest level to the
least deep level.
F3 (F2 (F1 (col, arg1), arg2), arg3)
Step 1 = Result 1
Step 2 = Result 2
Step 3 = Result 3
Nesting Functions
SQL > SELECT enam,
2 NVL (TO_CHAR (mgr), ‘No Manager’)
3 FROM emp
4 WHERE mgr IS NULL;
ENAME NVL(TO_CHAR (MGR), ‘NO MANAGER’)
KING No Manager
Summary
Use functions to:
• Perform calculations on data
• Modify individual data items
• Manipulate output for groups of rows
• Alter date formats for display
• Convert column datatypes
Practice Overview
• Creating queries that require the use of numeric,
character, and date functions.
• Using concatenation with functions
• Writing case-insensitive queries to test the usefulness of
character functions.
• Performing calculations of years and months of service for
an employee
• Determining the review date for an employee.
