The conversion functions convert a value from one data type
to another.
Data type conversion:
Ø The
Explicit conversion functions are
o
TO_CHAR à To
Character conversion
o
TO_DATE à To
Date conversion
o
TO_NUMBER à
To Number conversion
To_CHAR conversion function:
Ø This function can be used in two
different flavors.
o
To_Char
(number conversion)
o
To_Char(date
conversion)
o
To_CHAR
(Number conversion)
Syntax: To_Char(number,
fmt, ‘nlsparams’)
Ø Converts number of number data type
to a value of varchar2 data type.
Ø ‘fmt’ is the optional number format,
that can be used.
Ø The ‘nlsparms’ specifies the
characters returned by the number format element.
To_char (date conversion)
Syntax: To_char(date,
fmt,’nlsparams’)
Ø Converts date of date data type to a
value of varchar2 data type in the format specified.
Ø ‘fmt’ is the optional date format,
that can be used.
Ø The ‘nlsparams’ specifies the
language in which month and day names and abbreviations are returned.
Working with number format models:
Decimal Indicator: D à 99D99
Ø It returns the specified position of
the decimal character.
Ø The default decimal delimiter is ‘.’
Ø Only one decimal indicator can be
specified in a number format model.
Ex:
- Select 1234, to_char(1234,’9999D99’) from dual;
- Select
1234, to_char(1234,’999D99’)
from dual;
Scientific notation indicator: EEEE à 9.9 EEEE
Ø Returns a numeric value using
scientific notation.
Ex:
- Select 1234, to_char(1234,’9.9EEEE’), to_char(1234,’9.99EEEE’) from dual;
Group separator: G à 9G999
Ø Returns the specified position of
the group separator
Ø Multiple group separators can be
specified
Ex:
·
Select
1234567, to_char(1234567,’99G99G999’) from dual;
·
Select
sal, to_char(sal, ’99G999D99’) from emp;
Local Currency Indicator: L à L999 OR 999L
Ø It Returns the specified position of
the local currency symbol.
Ex:
·
Select
1234, to_char(1234,’L9999’) from dual;
·
Select
sal, to_char(sal,’L999999’) currency from emp;
·
Select
Sal, to_char(sal,’L99G999D99’,’NLS_CURRENCY = IndRupees’) sal from emp;
Trailing Minus Indicator: MI à 9999MI
Ø It returns negative value with a
trailing minus sign.
Ø It returns positive value with a
trailing blank.
Ø ‘MI’ format should be declared as
trailing argument only.
Ex:
·
Select
-1000, to_Char(-1000,’L99G999D99MI’) from dual;
·
Select
sal, comm, to_char(comm – sal,’L99999MI’) from emp;
Negative Number Indicator: PR à 9999PR
Ø Returns negative number in
‘<>’
Ø It can appear only as trailing
declaration.
Ex:
·
Select
to_char(-1000,’L99G999D99PR’) from dual;
·
Select
sal,comm, to_char(comm –sal,’L99G999D99PR’) from emp;
Roman Number Indicator: RN or rn
Ø RN à returns upper roman number.
Ø rn
à returns lower roman number.
Ø The value can be an integer between
1 and 3999.
Ex:
·
Select
1000, to_char(1000,’RN’), to_char(1000,’rn’) from dual;
Sign Indicator: S à S99999 OR 99999S
Ø Returns negative value with a
leading minus sign.
Ø Returns positive value with a
leading plus sign.
Ø ‘S’ can appear as first or last
value.
Ex:
·
Select
1000, to_char(1000,’S9999’) from dual;
·
Select
to_char(1000,’9999S’), to_char(-1000,’9999S’) from dual;
·
Select
sal, to_char(sal,’S99999’),to_char(sal,’99999S’) from emp;
·
Select
sal, comm, to_char(comm – sal,’S99999’), to_char(comm – sal,’99999S’) from emp;
Hexadecimal Indicator: X à XXXX
Ø Returns the hexadecimal value of the
specified number of digits.
Ø If the number is not an integer,
oracle rounds it to an integer.
Ø Accepts only positive values OR 0.
Ex:
·
Select
1000, to_char(1000,’XXXX’) from dual;
·
Select
ename, sal, to_char(sal,’xxxxx’) hexsal from emp;
Group Separator: , à 9,999
Ø Returns a comma in the specified
position.
Ø Multiple commas can be specified.
Ex:
·
Select
10000, to_char(10000,’99,999.99’) from dual;
·
Select
ename, sal, to_char(sal, ’99,999.99’) from emp;
Decimal Indicator: . à 99.99
Ø Returns a decimal point, at the
specified position.
Ø Only one period can be specified in
a number format model.
Ex:
·
Select
10000, to_char(10000,’L99,999.99’) from dual;
·
Select
Ename, Sal, to_char(sal,’L99,999.99’) from emp;
Dollar Indicator: $ à $9999
Ø Returns value with a leading dollar
sign.
Ex:
·
Select
10000, to_char(10000,’$99,999.99’) from dual;
·
Select
ename, sal, to_char(sal,’$99,999.99’) from emp;
Zero Indicator: 0 à 0999 OR 9990
Ø Returns leading OR trailing zeros.
Ex:
·
Select
10000,to_char(1000,’0999999’), to_char(1000,’09999990’) from dual;
·
Select
ename, sal, to_char(sal,’$099,999.99’) from emp;
Digit place Marker: 9 à 9999
Ø Returns value with a specified
number of digits with a leading space when positive or leading minus when
negative.
Ex:
·
Select
1000, 600, to_char(1000-600,’99999’), to_char(600-1000,’99999’) from dual;
·
Select
20.25, 20, to_char(20.25 – 20,’99999’) from dual;
ISO Currency Indicator: C à C999
Ø Returns specified position of the
ISO currency symbol.
Ex:
·
Select
1000,to_char(1000,’C9999.99’) from dual;
·
Select
ename, sal, to_char(sal,’C9999.99’) from emp;
Date Format Models:
Ø The date format models can be used
in the To_char function to translate a date value from original format to user
format.
Ø The total length of a date format
model cannot exceed 22 characters.
Date Format Elements:
Ø A date format model is composed of
one or more date format Elements.
Ø For input format models, format
items cannot appear twice, and format items that represent similar information
cannot be combined.
Ø Capitalization In a spelled word,
abbreviation, or Roman numeral follows capitalization in the corresponding
format element.
Ø Punctuation such as hyphens,
slashes, commas, periods and colons.
AD or A.D / BC or B.C Indicator:
Ø Indicates AD/BC with or without
periods.
Ex:
·
Select
sysdate, to_char(sysdate,’AD’) from dual;
·
Select
to_char(sysdate,’B.C.’), to_char(sysdate,’A.D.’) from dual;
·
Select
ename, sal, hiredate, to_char(hiredate,’A.D.’) from emp;
Meridian Indicator: AM or A.M. / PM or P.M.
Ø It indicates meridian indicator with
or without periods.
Ex:
·
Select
sysdate, to_char(sysdate, ’A.M.’), to_char(sysdate,’PM’) from dual;
·
Select
ename, sal, hiredate, to_char(hiredate,’AM’) from emp;
Century Indicator: CC/SCC
Ø Indicates the century, S prefixes BC
Date with ‘-‘.
Ex:
·
Select
sysdate, to_char(sysdate,’CC-AD’) from dual;
·
Select
sal, hiredate, to_char(hiredate,’SCC-AD’) from emp;
Numeric week Day Indicator: D à (1 – 7)
Ø Returns the week day number
Ex:
·
Select
sysdate,to_char(sysdate,’D’) from dual;
·
Select
Ename, hiredate, to_char(hiredate, ‘D’) from emp;
Week Day spelling Indicator: à Day
Ø Pads to a length of 9 characters.
Ex:
·
Select
sysdate, to_char(sysdate,’DAY’) from dual;
·
Select
sal, hiredate, to_char(hiredate,’DAY’) from emp where to_char(hiredate,’DAY’)
=’WEDNESDAY’;
Month Day Indicator: DD
Ø It indicates the day of the
month(1-31)
Ex:
·
Select
sysdate, to_char(sysdate,’DD-DAY’) from dual;
·
Select
hiredate, to_char(hiredate,’DD-DAY’) from emp;
·
Select
hiredate, to_char(hiredate,’DD-DAY’) from emp where to_char(hiredate,’DD-DAY’)
= ’03-WEDNESDAy’);
Year Day Indicator: DDD
Ø It indicates the day of the year (1
– 366)
Ex:
·
Select
sysdate, to_char(sysdate,’DDD’) from dual;
·
Select
Ename, hiredate, to_char(hiredate,’DDD’) from emp where to_char(hiredate,’DAY’)
=’WEDNESDAY’;
Abbreviated Week Day: DY
Ø It indicates the abbreviated name of
the week day.
Ex:
·
Select
sysdate, to_char(sysdate,’D-DY-DAY’) from dual;
·
Select
ename, hiredate, to_char(hiredate,’D-DY-DAY’) from emp;
ISO Standard Year Week Indicator: IW
Ø Specifies the week of the year (1 –
52 or 1 – 53) based on the ISO standard.
Ex:
·
Select
sysdate, to_char(sysdate,’IW’) from dual;
·
Select
ename, hiredate, to_char(hiredate,’IW’) from emp;
ISO Standard 4 Digit Year Indicator: IYYY
Ø Specifies 4 digits year based on the
ISO standard.
Ø It can even be used in combination
of IYY, IY, I.
Ex:
·
Select
sysdate, to_char(sysdate,’IYYY’) from dual;
·
Select
ename, hiredate, to_Char(hiredate,’IYYY’) from emp where
to_char(hiredate,’DAY’) =’WEDNESDAY’;
Four Digit Year Indicators: YYYY OR SYYYY
Ø Returns four digit year, ‘S’
prefixes BC dates with ‘-‘.
Ø It can even be used in combination
of YYY or YY or Y.
Ø Y, YYY returns year with comma in
that position.
Ex:
·
Select
sysdate, to_char(sysdate,’YYYY’) four, to_char(sysdate,’YYY’) three from dual;
·
Select
ename, hiredate, to_char(hiredate,’YYYY’) from emp;
Spelled year Indicator: YEAR OR SYEAR
Ø Returns the numerical year in
spelling.
Ø ‘S’ prefixes BC Dates with ‘-‘.
Ex:
·
Select
sysdate, to_Char(sysdate,’YEAR’) from dual;
·
Select
Ename, hiredate, to_char(hiredate,’YEAR’) from emp;
Week of the month Indicator: W
Ø Specifies the week of the month (1 -
5).
Ø Week starts on the first day of the
earth month and ends on the seventh day.
Ex:
·
Select
sysdate, to_char(sysdate,’W’) from dual;
·
Select
ename, hiredate, to_char(hiredate,’W’) from emp;
Year Week Indicator: WW
Ø Specifies the week of the year (1 –
53).
Ø Week 1 starts on the first day of
the year and continues to the seventh day in that year.
Ex:
·
Select
sysdate, to_char(sysdate,’WW’) from dual;
·
Select
ename, hiredate, to_char(hiredate,’WW’) from emp;
Quarter of the Year Indicator: Q
Ø Returns the quarter of the year.
Ø Quarter starting with the month of
January and ending with every three months.
Ex:
·
Select
sysdate, to_char(sysdate,’Q’) from dual;
·
Select
ename, hiredate, to_char(hiredate,’Q’) From emp where to_char(hiredate,’Q’) =
4;
Julian Day Indicator: J
Ø Returns the Julian day of the given
date.
Ø It is the number of days since
January 1, 4712 BC.
Ø Numbers specified with ‘J’ must be
integers.
Ex:
·
Select
sysdate, to_char(sysdate,’J’) from dual;
·
Select
Ename, to_char(hiredate,’J-DDD-DD-D’) From emp;
Numeric Month Indicator: MM
Ø Returns the numeric abbreviation of
the month.
Ex:
·
Select
sysdate, to_char(sysdate,’MM-YYYY’) from dual;
·
Select
ename, hiredate, to_char(hiredate,’DD-MM-YYYY’) From emp where
to_char(hiredate,’MM’) = 12;
Abbreviated Month Indicator: MON
Ø Returns the abbreviated name of the
month.
Ex:
·
Select
sydate, to_char(sysdate,’MM-MON’) from dual;
Month Spelling Indicator: MONTH
Ø Spells the name of the month, padded
to a length of 9 characters.
Ex:
·
Select
sysdate, to_char(sysdate,’MON-MONTH’) From dual;
·
Select
ename, hiredate, to_char(hiredate,’MONTH, YYYY’) from emp;
Twelve Hour Clock Mode: HH or HH12
Ø Returns the hour of the day in
twelve hour clock mode.
Ex:
·
Select
sysdate, to_char(sysdate,’HH’), to_char(sysdate,’HH12, AM’) from dual;
·
Select
ename, hiredate, to_char(hiredate,’HH12 : AM’) from emp;
Twenty Hour Clock Mode: HH24
Ø Returns the hour of the day in
twenty four hour clock mode. (0 – 23)
Ex:
·
Select
sysdate, to_char(sysdate,’HH24’) from dual;
Minutes Indicator: MI
Ø Returns the minutes from the given
date (0 – 59).
Ex:
·
Select
sysdate, to_char(sysdate,’MI’), to_char(sysdate,’HH:MI’) from dual;
·
Select
Ename, sal, to_char(hiredate,’HH:MI’) from emp where job =’CLERK’;
Roman Month Indicator: RM
Ø Returns the roman numerical month (I
– XII).
Ex:
·
Select
sysdate, to_char(sysdate,’RM’), to_char(sysdate,’DD-RM-YY’) from dual;
·
Select
ename, sal, to_char(hiredate,’DD-RM-YY’) From emp;
Seconds Indicator: SS
Ø Returns seconds from the given date
(0 – 59).
Ex:
·
Select
sysdate, to_char(sysdate,’SS’), to_char(sysdate,’HH:MI:SS’) from dual;
·
Select
sysdate, to_char(sysdate,’DD-MONTH-YYYY, HH:MI:SS A.M.’) from dual;
·
Select
ename, sal, hiredate, to_char(hiredate,’HH24:MI:SS’) from emp;
Seconds Past Mid Night: SSSSS
Ø Display seconds past midnight (0 –
86399).
Ex:
·
Select
sysdate, to_char(sysdate,’SSSSS’) from dual;
Date Format Punctuators:
Ø The Punctuation marks that can be
used in date formats are…
Ø ‘-‘, ‘/’, ‘!’, ‘.’ , ‘;’, ‘:’,
“text”
Date Format Elements Suffixes: TH OR SP
TH à
Suffixes the Ordinal number with ‘ST’ or ‘ND’ or ‘RD’ or ‘TH’.
Example: DDTH à 20TH.
Ex:
·
Select
sysdate, to_char(sysdate,’DDTH,MONTH,YYYY’) from dual;
·
Select
ename, sal, hiredate, to_char(hiredate,’DDTH,MONTH,YYYY’) from emp;
Sp à
Spells Ordinal Numbers.
Example: DDSP à
TWENTY
·
Select
sysdate, to_char(sysdate,’DDSP, Month, YYY’) from dual;
·
Select
ename, sal, to_char(hiredate,’DDSP,Month,YYYY’) from emp;
·
Select
sysdate, to_char(sysdate,’DDSPTH, Month,YYYY’) from dual;
·
Select
ename, sal, to_char(hiredate,’DDSPTH, Month, YYYY’) from emp;
·
Select
sysdate, to_char(sysdate,’DDSPTH Month YYYYSP’) from dual;
Date format Elements Restrictions:
Ø The suffixes when added to date
return values always in English.
Ø Date suffixes are valid only on
output, hence cannot be used to insert a date into the database.
Format Model Modifiers:
Fill Mode Indicator: FM
Ø It suppresses blank padding in the
return value of the to_char function.
Format Exact: FX
Ø It specifies exact matching for the
character argument and date format model.
Ex:
· Select
sysdate, to_char(sysdate,’DDSPTH MONTH YYYYSP’), to_char(sysdate,’FMDDSPTH MONTH
YYYYSP’) from dual;
TO_NUBMER Function:
Syntax: TO_NUBMER
(char, fmt, ‘nlsparam’)
Ø It converts a char, value of CHAR or
varchar2 data type containing a number in the format specified by the optional
format model ‘fmt’ to a value of number data type.
Ex:
·
Select
’$10,000.00’, to_number(’$10,000.00’,’L99,999.99’) from dual;
·
Select
’$1,000.00’ to_number(’$1,0000.00’,’L9,999.99’) + 500 from dual;
TO_DATE Function:
Syntax: TO_DATE(char,
fmt, ‘nlsparam’)
Ø Converts given char of CHAR or
VARCHAR2 data type to a value of DATE data type.
Ø The ‘fmt’ is an optional date format
specifying the format of char.
Ex:
·
Select
ename, hiredate, add_months(to_date(’17-DEC-1980’,’DD-MON-YY’),3) from emp
where hiredate = ’17-DEC-1980’;
·
Select
ename, hiredate,add_months(to_date(’1980-DECEMBER-17’,’YYYY-MONTH-DD’),3) from
emp where hiredate = ’17-DEC-1980’;
·
Select
ename, hiredate, add_months(to_date(’1980-DECEMBER-17’,’YYYY-MONTH-DD’),3) from
emp where to_char(hiredate,’FMYYYY-MONTH-DD’) =’1980-DECEMBER-17’);
·
Select
’12-August-2007’,to_date(’12-August-2007’,’DD-Month-YYYY’) + 3 from dual;
Let Us Revisit INSERT Statement Once Again:
Working With Invalid Numbers:
Ex:
·
Create
table sampins
(
SampleNum number(6),
SampleDate Date
);
·
Insert
into sampins(SampleNum,SampleDate) alues(to_number(’1,23,456’,’9G99G999’),
sysdate);
·
Insert
into sampins(SampleNum, SampleDate) values(to_number(’1,23,457-‘,’9G99G999MI’),sysdate);
Working With Invalid Dates:
Ex:
·
Insert
into sampins(SampleNum, SampleDate) values(123458,’02-AUG-07’);
·
Insert
into sampins(SampleNum, SampleDate) values(123459,sysdate);
·
Insert
into Sampins(SampleNum, SampleDate) values(123460, to_date(’02-August-2007,06:45:36
P.M.’,’DD-Month-YYYY,HH:MI:SS P.M.’);
Spelling a Number:
Ex:
·
Select
to_char(to_date(’&GiveNumber’,’J’),’JSP’) “splled number” from dual;