Dates and Times

Formatting Times

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

 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12344.html]SQL Snippets: Dates and Times - Formatting Times[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12344.html">SQL Snippets: Dates and Times - Formatting Times</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Dates and Times - Formatting Times
  • URL (href): http://www.sqlsnippets.com/en/topic-12344.html