SQL Snippets ™: Tutorials for Oracle Developers

Punctuation

The following punctuation characters are reproduced "as is" when they appear in format models.

- / , . ; :
 

Text enclosed in double quotes (") is reporduced "as is" when it appears in format models too.

Here is an example that uses both punctuation and double quoted text in the format mask.

select to_char( sysdate, '"Year": YYYY, "Month": MM' ) as test
from dual ;
 

TEST
---------------------
Year: 2007, Month: 06
 

Local Radix Character

The "X" format element outputs the appropriate radix character (the character that separates the integer portion of a number from the fractional portion) for the current combination of the NLS_LANGUAGE and NLS_TERRITORY session parameters.

Here are some examples where the "X" format element is used in conjunction with the SS and FF elements.

alter session set NLS_LANGUAGE  = 'GERMAN'  ;
alter session set NLS_TERRITORY = 'GERMANY' ;
 

select to_char( systimestamp, 'SSXFF' ) as "Output" from dual ;
 

Output
------------
38,740000
 

alter session set NLS_LANGUAGE  = 'AMERICAN' ;
alter session set NLS_TERRITORY = 'AMERICA'  ;
 

select to_char( systimestamp, 'SSXFF' ) as "Output" from dual ;
 

Output
------------
38.750000
 

Changing NLS_LANGUAGE and NLS_TERRITORY also affects the NLS_DATE_FORMAT and NLS_TIMESTAMP_% session parameters. Before proceeding we should reset their values back those used in the Setup topic for this section.

alter session set NLS_DATE_FORMAT         = 'SYYYY-MM-DD'                    ;
alter session set NLS_TIMESTAMP_FORMAT    = 'SYYYY-MM-DD HH24:MI:SS'         ;
alter session set NLS_TIMESTAMP_TZ_FORMAT = 'SYYYY-MM-DD HH24:MI:SS TZH:TZM' ;
 

Gotchas

"X" does not work with DATE datatypes.

select to_char( SYSDATE, 'SSXFF' ) as output from dual ;
select to_char( SYSDATE, 'SSXFF' ) as output from dual
                         *
ERROR at line 1:
ORA-01821: date format not recognized