Oracle Database Converting Dates to a String


Example

Use TO_CHAR( date [, format_model [, nls_params]] ):

(Note: if a format model is not provided then the NLS_DATE_FORMAT session parameter will be used as the default format model; this can be different for every session so should not be relied on. It is good practice to always specify the format model.)

CREATE TABLE table_name (
  date_value DATE
);

INSERT INTO table_name ( date_value ) VALUES ( DATE '2000-01-01' );
INSERT INTO table_name ( date_value ) VALUES ( TIMESTAMP '2016-07-21 08:00:00' );
INSERT INTO table_name ( date_value ) VALUES ( SYSDATE );

Then:

SELECT TO_CHAR( date_value, 'YYYY-MM-DD' ) AS formatted_date FROM table_name;

Outputs:

FORMATTED_DATE
--------------
2000-01-01
2016-07-21
2016-07-21

And:

SELECT TO_CHAR(
         date_value,
         'FMMonth d yyyy, hh12:mi:ss AM',
         'NLS_DATE_LANGUAGE = French'
       ) AS formatted_date
FROM   table_name;

Outputs:

FORMATTED_DATE
-----------------------------
Janvier   01 2000, 12:00:00 AM
Juillet   21 2016, 08:00:00 AM
Juillet   21 2016, 19:08:31 PM