In this topic we apply various Time Format Elements to some test times and see what output they produce. The "Output" column contains the results of a function call like
TO_CHAR( (Input), '(Format Element)' )
Globalized Time Formats
The following time format element will display times in whatever format is standard for the current combination of the NLS_LANGUAGE and NLS_TERRITORY session parameters. See the entries for TS at Punctuation and Character Literals in Datetime Format Models for more details.
Format Element Note ------- --------------------------------------------------------------------------- TS Time - short format
Here is some sample output from these format elements.
alter session set NLS_LANGUAGE = 'GERMAN' ; alter session set NLS_TERRITORY = 'GERMANY' ;
Format Element Input Output ------- ---------- ------------------------------ TS 00:00:00 00:00:00 TS 01:10:11 01:10:11 TS 11:23:58 11:23:58 TS 12:00:00 12:00:00 TS 12:34:56 12:34:56 TS 14:30:00 14:30:00 TS 22:00:00 22:00:00
alter session set NLS_LANGUAGE = 'AMERICAN' ; alter session set NLS_TERRITORY = 'AMERICA' ;
Format Element Input Output ------- ---------- ------------------------------ TS 00:00:00 12:00:00 AM TS 01:10:11 1:10:11 AM TS 11:23:58 11:23:58 AM TS 12:00:00 12:00:00 PM TS 12:34:56 12:34:56 PM TS 14:30:00 2:30:00 PM TS 22:00:00 10:00:00 PM
Changing NLS_LANGUAGE and NLS_TERRITORY also affects the NLS_DATE_FORMAT and NLS_TIMESTAMP_% session parameters. Before proceeding we will reset their values back to those specified 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' ;
Meridian Indicators
The meridian format elements are:
Format Element Note ------- --------------------------------------------------------------------------- A.M. meridian indicator AM meridian indicator P.M. meridian indicator PM meridian indicator
Here is some sample output from these format elements.
Format Element Input Output ------- ---------- ---------- A.M. 00:00:00 A.M. A.M. 01:10:11 A.M. A.M. 11:23:58 A.M. A.M. 12:00:00 P.M. A.M. 12:34:56 P.M. A.M. 14:30:00 P.M. A.M. 22:00:00 P.M. AM 00:00:00 AM AM 01:10:11 AM AM 11:23:58 AM AM 12:00:00 PM AM 12:34:56 PM AM 14:30:00 PM AM 22:00:00 PM |
Format Element Input Output ------- ---------- ------------------------------ P.M. 00:00:00 A.M. P.M. 01:10:11 A.M. P.M. 11:23:58 A.M. P.M. 12:00:00 P.M. P.M. 12:34:56 P.M. P.M. 14:30:00 P.M. P.M. 22:00:00 P.M. PM 00:00:00 AM PM 01:10:11 AM PM 11:23:58 AM PM 12:00:00 PM PM 12:34:56 PM PM 14:30:00 PM PM 22:00:00 PM |
Hours
The hour format elements are:
Format Element Note ------- --------------------------------------------------------------------------- HH24 hour of day (0-23) HH hour of day (1-12) HH12 hour of day (1-12)
Here is some sample output from these format elements.
Format Element Input Output ------- ---------- ---------- HH 00:00:00 12 HH 01:10:11 01 HH 11:23:58 11 HH 12:00:00 12 HH 12:34:56 12 HH 14:30:00 02 HH 22:00:00 10 HH12 00:00:00 12 HH12 01:10:11 01 HH12 11:23:58 11 HH12 12:00:00 12 HH12 12:34:56 12 HH12 14:30:00 02 HH12 22:00:00 10 |
Format Element Input Output ------- ---------- ---------- HH24 00:00:00 00 HH24 01:10:11 01 HH24 11:23:58 11 HH24 12:00:00 12 HH24 12:34:56 12 HH24 14:30:00 14 HH24 22:00:00 22 |
Minutes
The minute format element is:
Format Element Note ------- --------------------------------------------------------------------------- MI minute of hour (0-59)
Here is some sample output from this format element.
Format Element Input Output ------- ---------- ---------- MI 00:00:00 00 MI 01:10:11 10 MI 11:23:58 23 MI 12:00:00 00 MI 12:34:56 34 MI 14:30:00 30 MI 22:00:00 00
Seconds
The format elements for seconds are:
Format Element Note ------- --------------------------------------------------------------------------- SSSSS seconds past midnight (0-86399) SS second of minute (0-59)
Here is some sample output from these format elements.
Format Element Input Output ------- ---------- ---------- SS 00:00:00 00 SS 01:10:11 11 SS 11:23:58 58 SS 12:00:00 00 SS 12:34:56 56 SS 14:30:00 00 SS 22:00:00 00 |
Format Element Input Output ------- ---------- ---------- SSSSS 00:00:00 00000 SSSSS 01:10:11 04211 SSSSS 11:23:58 41038 SSSSS 12:00:00 43200 SSSSS 12:34:56 45296 SSSSS 14:30:00 52200 SSSSS 22:00:00 79200 |
Fractional Seconds
The format elements for fractional seconds are:
Format Element Note ------- --------------------------------------------------------------------------- FF1 fractional seconds (1 digit precision) FF2 fractional seconds (2 digits precision) FF3 fractional seconds (3 digits precision) FF4 fractional seconds (4 digits precision) FF5 fractional seconds (5 digits precision) FF6 fractional seconds (6 digits precision) FF7 fractional seconds (7 digits precision) FF8 fractional seconds (8 digits precision) FF9 fractional seconds (9 digits precision)
Here is some sample output from these format elements.
Format Element Input Output ------- -------------------------- ---------- FF1 01:10:11.101010101 1 FF1 14:30:00.123456789 1 FF2 01:10:11.101010101 10 FF2 14:30:00.123456789 12 FF3 01:10:11.101010101 101 FF3 14:30:00.123456789 123 FF4 01:10:11.101010101 1010 FF4 14:30:00.123456789 1234 FF5 01:10:11.101010101 10101 FF5 14:30:00.123456789 12345 FF6 01:10:11.101010101 101010 FF6 14:30:00.123456789 123456 FF7 01:10:11.101010101 1010101 FF7 14:30:00.123456789 1234567 FF8 01:10:11.101010101 10101010 FF8 14:30:00.123456789 12345678 FF9 01:10:11.101010101 101010101 FF9 14:30:00.123456789 123456789
Time Zones
The time zone format elements are:
Format Element Note ------- --------------------------------------------------------------------------- TZH time zone hour TZM time zone minute TZR time zone region TZD daylight savings time indicator
Here is some sample output from these format elements.
Format Element Input Output ------- -------------------------- ------ TZH 00:00 +05:30 +05 TZH 01:10 AMERICA/NEW_YORK EST -05 TZH 11:23 ASIA/TEHRAN IRST +03 TZH 12:00 +00:00 +00 TZH 12:00 EUROPE/TALLINN EET +02 TZH 12:34 +03:00 +03 TZH 14:30 CANADA/YUKON PDT -07 TZH 22:00 -12:00 -12 TZM 00:00 +05:30 30 TZM 01:10 AMERICA/NEW_YORK EST 00 TZM 11:23 ASIA/TEHRAN IRST 30 TZM 12:00 +00:00 00 TZM 12:00 EUROPE/TALLINN EET 00 TZM 12:34 +03:00 00 TZM 14:30 CANADA/YUKON PDT 00 TZM 22:00 -12:00 00 |
Format Element Input Output ------- -------------------------- ---------------- TZR 00:00 +05:30 +05:30 TZR 01:10 AMERICA/NEW_YORK EST AMERICA/NEW_YORK TZR 11:23 ASIA/TEHRAN IRST ASIA/TEHRAN TZR 12:00 +00:00 +00:00 TZR 12:00 EUROPE/TALLINN EET EUROPE/TALLINN TZR 12:34 +03:00 +03:00 TZR 14:30 CANADA/YUKON PDT CANADA/YUKON TZR 22:00 -12:00 -12:00 TZD 00:00 +05:30 TZD 01:10 AMERICA/NEW_YORK EST EST TZD 11:23 ASIA/TEHRAN IRST IRST TZD 12:00 +00:00 TZD 12:00 EUROPE/TALLINN EET EET TZD 12:34 +03:00 TZD 14:30 CANADA/YUKON PDT PDT TZD 22:00 -12:00 |