Oracle TO_CHAR (datetime) function

Last update on September 21 2024 12:32:13 (UTC/GMT +8 hours)

Description

The TO_CHAR (datetime) function is used to convert a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype into a string representation (VARCHAR2 datatype) based on a specified format.

Uses of Oracle TO_CHAR (datetime) Function:

Formatting Dates: Convert DATE and TIMESTAMP values into customized string formats for better presentation.

Including Time Zones: Convert TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE values while considering the session's time zone settings.

Handling Different Languages: Use NLS parameters to format month and day names in various languages, catering to diverse user bases.

Data Reporting: Generate formatted date and time strings for reports, improving data clarity in analytical outputs.

Interval Representation: Convert interval values into readable formats for applications that require interval data interpretation.

Syntax:

TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])

Parameters:

Name	Description
datetime \| interval	A date or number that will be converted to a string.
fmt (Optional)	The format that will be used to convert the value to a string. If no format (fmt) is present, then date is converted to a VARCHAR2 value as follows : DATE values are converted to values in the default date format. TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE values are converted to values in the default timestamp format. TIMESTAMP WITH TIME ZONE values are converted to values in the default timestamp with time zone format.
nlsparam	Specifies the language in which month and day names and abbreviations are returned. This argument can have this form : 'NLS_DATE_LANGUAGE = language'

Applies to:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Examples: Oracle TO_CHAR (datetime) function

The following example uses this table:

CREATE TABLE date_tab (
   ts_col      TIMESTAMP,
   tsltz_col   TIMESTAMP WITH LOCAL TIME ZONE,
   tstz_col    TIMESTAMP WITH TIME ZONE);

The example shows the results of applying TO_CHAR to different TIMESTAMP datatypes. The result for a TIMESTAMP WITH LOCAL TIME ZONE column is sensitive to session time zone, whereas the results for the TIMESTAMP and TIMESTAMP WITH TIME ZONE columns are not sensitive to session time zone:

ALTER SESSION SET TIME_ZONE = '-8:00';
INSERT INTO date_tab VALUES (  
   TIMESTAMP'1999-12-01 10:00:00',
   TIMESTAMP'1999-12-01 10:00:00',
   TIMESTAMP'1999-12-01 10:00:00');
INSERT INTO date_tab VALUES (
   TIMESTAMP'1999-12-02 10:00:00 -8:00', 
   TIMESTAMP'1999-12-02 10:00:00 -8:00',
   TIMESTAMP'1999-12-02 10:00:00 -8:00');

SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS ts_date,
   TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') AS tstz_date
   FROM date_tab
   ORDER BY ts_date, tstz_date;
 
TS_DATE                        TSTZ_DATE
------------------------------ -------------------------------------
01-DEC-1999 10:00:00.000000    01-DEC-1999 10:00:00.000000 -08:00
02-DEC-1999 10:00:00.000000    02-DEC-1999 10:00:00.000000 -08:00

SELECT SESSIONTIMEZONE, 
   TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS tsltz
   FROM date_tab
   ORDER BY sessiontimezone, tsltz;

SESSIONTIM TSLTZ
---------- ------------------------------
-08:00     01-DEC-1999 10:00:00.000000
-08:00     02-DEC-1999 10:00:00.000000

ALTER SESSION SET TIME_ZONE = '-5:00';
SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS ts_col,
   TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') AS tstz_col
   FROM date_tab
   ORDER BY ts_col, tstz_col;
 
TS_COL                         TSTZ_COL
------------------------------ -------------------------------------
01-DEC-1999 10:00:00.000000    01-DEC-1999 10:00:00.000000 -08:00
02-DEC-1999 10:00:00.000000    02-DEC-1999 10:00:00.000000 -08:00

SELECT SESSIONTIMEZONE,
TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS tsltz_col
   FROM date_tab
   ORDER BY sessiontimezone, tsltz_col;
  2    3    4
SESSIONTIM TSLTZ_COL
---------- ------------------------------
-05:00     01-DEC-1999 13:00:00.000000
-05:00     02-DEC-1999 13:00:00.000000

Previous: SYSTIMESTAMP
Next: TO_TIMESTAMP