Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
Functions, 131 of 166
to_char_date::=
to_char_date
TO_CHAR
converts date
of DATE
, TIMESTAMP
, TIMESTAMP
WITH
TIME
ZONE
, or TIMESTAMP
WITH
LOCAL
TIME
ZONE
datatype to a value of VARCHAR2
datatype in the format specified by the date format fmt
. If you omit fmt
, date
is converted to a VARCHAR2
value as follows:
DATE
is converted to a value in the default date format.
TIMESTAMP
and TIMESTAMP
WITH
LOCAL
TIME
ZONE
are converted to values in the default timestamp format.
TIMESTAMP
WITH
TIME
ZONE
is converted to a value in the default timestamp with time zone format.
The 'nlsparams
' specifies the language in which month and day names and abbreviations are returned. This argument can have this form:
'NLS_DATE_LANGUAGE = language'
If you omit nlsparams
, this function uses the default date language for your session.
The following example uses this table:
CREATE TABLE my_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 my_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 my_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'), TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') FROM my_tab; TO_CHAR(TS_COL,'DD-MON-YYYYHH2 TO_CHAR(TSTZ_COL,'DD-MON-YYYYHH24:MI: ------------------------------ ------------------------------------- 01-DEC-1999 10:00:00 01-DEC-1999 10:00:00.000000 -08:00 02-DEC-1999 10:00:00 02-DEC-1999 10:00:00.000000 -08:00 SELECT SESSIONTIMEZONE, TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF') FROM my_tab; SESSION TO_CHAR(TSLTZ_COL,'DD-MON-YYYY ------- ------------------------------ -08:00 01-DEC-1999 10:00:00 -08:00 02-DEC-1999 10:00:00 ALTER SESSION SET TIME_ZONE = '-5:00'; SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF'), TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') FROM my_tab; TO_CHAR(TS_COL,'DD-MON-YYYYHH2 TO_CHAR(TSTZ_COL,'DD-MON-YYYYHH24:MI: ------------------------------ ------------------------------------- 01-DEC-1999 10:00:00 01-DEC-1999 10:00:00.000000 -08:00 02-DEC-1999 10:00:00 02-DEC-1999 10:00:00.000000 -08:00 SELECT SESSIONTIMEZONE, TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF') FROM my_tab; SESSION TO_CHAR(TSLTZ_COL,'DD-MON-YYYY ------- ------------------------------ -05:00 01-DEC-1999 13:00:00 -05:00 02-DEC-1999 13:00:00
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|