1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle/PLSQL: TO_DATE and TO_CHAR Functions

Discussion in 'SQL PL/SQL' started by SBH, Nov 22, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview

    Oracle provides conversion functions to change an input into compatible data type family. Below article explains the conversions and format masking done by TO_CHAR and TO_DATE functions.

    1. TO_CHAR

    TO_CHAR function is used to typecast a numeric or date input to character type.

    1.1. Number to Character conversion

    Numeric input is the mandatory input for the function along with the required format mask. A number can be formatted to specify width, currency symbol, decimal position, and group separators must be enclosed in single quotes.

    Syntax

    Code (Text):
    TO_CHAR(number1, [format], [nls_parameter]),
    [​IMG]

    1.2. Examples

    Code (SQL):
    SELECT  TO_CHAR('9920860448','TM') RES FROM DUAL;

    RES
    ------------
    992060448

    SELECT TO_CHAR('3990','RN')  RES FROM DUAL;

    RES
    -----------------
    MMMCMXC

    SELECT TO_CHAR('9920860','99,999,999') RES FROM DUAL;

    RES
    ------------
    9,920,860

    SELECT TO_CHAR('40012145','9999999999V999') RES FROM DUAL;

    RES
    -----------------
    40012145000

    1.3. Date to Character conversion

    Dates can be formatted in multiple formats after converting to character types using TO_CHAR function.

    Syntax
    Code (Text):
    TO_CHAR(date, [format], [nls_parameter])
    Below is a list of all the formats that can be used to format date values

    [​IMG]

    1.4. Example

    For the below examples, SYSDATE input is 12-AUG-2010

    Code (SQL):
    SELECT TO_CHAR(sysdate,'SCC') RES FROM DUAL;    

    RES
    ---
    21

    SELECT TO_CHAR(sysdate,'FMDAY') RES FROM DUAL; 

    RES
    --------
    THURSDAY

    SELECT TO_CHAR(sysdate,'BC') RES FROM DUAL;

    RE
    --
    AD

    SELECT TO_CHAR(sysdate,'Q') RES FROM DUAL;

    RE
    --
    3

    SELECT TO_CHAR(sysdate,'MONTH') RES FROM DUAL; 

    RES
    ------
    AUGUST

    SELECT TO_CHAR(sysdate,'DDD') RES FROM DUAL;   

    RES
    ---
    224

    SELECT TO_CHAR(sysdate,'DD') RES FROM DUAL;

    RE
    ---
    12

    SELECT TO_CHAR(sysdate,'D') RES FROM DUAL; 

    RE
    --
    5

    SELECT TO_CHAR(sysdate,'J') RES FROM DUAL; 

    RES
    -------
    2455421

    2. TO_DATE

    It takes character/number values as input and returns formatted date equivalent of the same.

    2.1. Character to Date conversion

    Date literals in character formats can be casted to Date type by through appropriate formatting.

    Syntax:
    Code (Text):
    TO_DATE( string1, [ format_mask ], [ nls_language ] )
    The following is a list of options for the format_mask parameter.
    [​IMG]
    The following example converts a character string into a date:

    Code (SQL):
    SELECT
    TO_DATE('January 15, 1989, 11:00 A.M.',
      'Month dd, YYYY, HH:MI A.M.',
      'NLS_DATE_LANGUAGE = American')
    FROM DUAL;
    TO_DATE(
    ---------
    15-JAN-89
    The value returned reflects the default date format if the NLS_TERRITORY parameter is set to 'AMERICA'. Different NLS_TERRITORY values result in different default date formats:

    Code (SQL):
    ALTER SESSION SET NLS_TERRITORY = 'KOREAN';

    SELECT
    TO_DATE('January 15, 1989, 11:00 A.M.',
                  'Month dd, YYYY, HH:MI A.M.',
       'NLS_DATE_LANGUAGE = American')
    FROM DUAL;
    TO_DATE(
    --------
    89/01/15
     

    Attached Files:

    • Tab1.jpg
      Tab1.jpg
      File size:
      101.6 KB
      Views:
      22,518
    • Tab2.jpg
      Tab2.jpg
      File size:
      103 KB
      Views:
      18,180
    • Tab3.jpg
      Tab3.jpg
      File size:
      96.2 KB
      Views:
      17,959