Conversion functions in SQL

 Conversion functions:

 Ø Conversion functions convert a value from one data type to another.

v TO_CHAR

v TO_DATE

v TO_NUMBER

To_Char conversion:

   TO_CHAR() function converts date value to a string in specified date format.

  Syntax:

1.   TO_CHAR(Date,’Format_method’)

 Explanation:

   Ø Format_method must be enclosed with the single quotes

   Ø Format_method should be Case sensitive.

Example:

1.    Select empno, ename, sal, TO_CHAR(hiredate,’DD-MM-YYYY’)

      From emp

      Where ename=’KING’;

 Output:

EMPNO	ENAME	SAL	HIREDATE
1001	KING	20000	10-09-2015

 Example:

1.    Select empno, ename, sal, TO_CHAR(hiredate,’MM/YY’)

      From emp

      Where ename=’KING’;

Output:

EMPNO	ENAME	SAL	HIREDATE
1001	KING	20000	09/10

Elements of Date format method:

METHOD	DESCRIPTION
DD	Numeric day of the month
DY	Three letter name of the day
DAY	Full name of the day
MM	Two digit value of the month
MON	Three letter name of the Month
MONTH	Full name of the month
YYYY	Full year in number
YEAR	Year name is spelledout

Some other examples:

1.    Select empno, ename, sal, TO_CHAR(hiredate,’fmdd Month yyyy’  )

      From emp

      Where ename=’KING’;

Output:

EMPNO	ENAME	SAL	HIREDATE
1001	KING	20000	09 September 2010

TO_CHAR finction with numbers:

 TO_CHAR number conversion converts the number into character format.

Syntax:

1.  TO_CHAR(number,’format_model’)

Example:

1. Select TO_CHAR(sal,’$99,999.00’) salary

   From emp

   Where ename=’KING’;

Output:

Salary

$5000

 TO_DATE() conversion:

   Ø This function used to return the character value to a date format.

   Ø Character value is the input and date format is the returned value.

Example:1

1. Select TO_DATE(’10-07-2012’,’DD-MM-YYYY’) from dual;

Example:2

1. Select TO_DATE(’ 2012-06-10’,’ YYYY-MM-DD’) from dual;

Example:3

1. Select TO_DATE('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS') from dual;

Here,

   HH24:MI:SS' This statement specifies the hours , minutes, seconds.

OR

 Select TO_DATE('2012/07/18 13:27:18', 'YYYY-MM-DD HH24:MI:SS') 

 from dual;

To_date in where condition:

1.  Select ename,hiredate

    From emp

   Where hiredate=To_date(’20/10/2000’,’DD-MON-YYYY’);

Output:

ENAME	HIREDATE
KING	20-DEC-2000

 TO_NUMBER() conversion:

    It converts a string into a number.

    To_number function returns numeric value.

Syntax:

1. To_number(‘String1’,’Format_method’)

Example:

1. Select To_number(‘130.23’,’9G999D99’)

   From dual;

Output:

To_number(‘130.23’,’9G999D99’)

130.23

 

 

 

 

Share: