Oracle Database Dates 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
