Sophie: postgresql8.4-docs-8.4.12-0.1mdv2010.2 i586

postgresql8.4-docs-8.4.12-0.1mdv2010.2.i586.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Data Type Formatting Functions</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.4.12 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Functions and Operators"
HREF="functions.html"><LINK
REL="PREVIOUS"
TITLE="Pattern Matching"
HREF="functions-matching.html"><LINK
REL="NEXT"
TITLE="Date/Time Functions and Operators"
HREF="functions-datetime.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2012-05-31T23:30:11"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.4.12 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions-matching.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 9. Functions and Operators</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions-datetime.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="FUNCTIONS-FORMATTING"
>9.8. Data Type Formatting Functions</A
></H1
><A
NAME="AEN11457"
></A
><A
NAME="AEN11459"
></A
><A
NAME="AEN11461"
></A
><A
NAME="AEN11463"
></A
><A
NAME="AEN11465"
></A
><P
>    The <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> formatting functions
    provide a powerful set of tools for converting various data types
    (date/time, integer, floating point, numeric) to formatted strings
    and for converting from formatted strings to specific data types.
    <A
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-TABLE"
>Table 9-20</A
> lists them.
    These functions all follow a common calling convention: the first
    argument is the value to be formatted and the second argument is a
    template that defines the output or input format.
   </P
><P
>    A single-argument <CODE
CLASS="FUNCTION"
>to_timestamp</CODE
> function is also
    available;  it accepts a
    <TT
CLASS="TYPE"
>double precision</TT
> argument and converts from Unix epoch
    (seconds since 1970-01-01 00:00:00+00) to
    <TT
CLASS="TYPE"
>timestamp with time zone</TT
>.
    (<TT
CLASS="TYPE"
>Integer</TT
> Unix epochs are implicitly cast to
    <TT
CLASS="TYPE"
>double precision</TT
>.)
   </P
><DIV
CLASS="TABLE"
><A
NAME="FUNCTIONS-FORMATTING-TABLE"
></A
><P
><B
>Table 9-20. Formatting Functions</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><COL><THEAD
><TR
><TH
>Function</TH
><TH
>Return Type</TH
><TH
>Description</TH
><TH
>Example</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_char</CODE
>(<TT
CLASS="TYPE"
>timestamp</TT
>, <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
>convert time stamp to string</TD
><TD
><TT
CLASS="LITERAL"
>to_char(current_timestamp, 'HH12:MI:SS')</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_char</CODE
>(<TT
CLASS="TYPE"
>interval</TT
>, <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
>convert interval to string</TD
><TD
><TT
CLASS="LITERAL"
>to_char(interval '15h&nbsp;2m&nbsp;12s', 'HH24:MI:SS')</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_char</CODE
>(<TT
CLASS="TYPE"
>int</TT
>, <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
>convert integer to string</TD
><TD
><TT
CLASS="LITERAL"
>to_char(125, '999')</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_char</CODE
>(<TT
CLASS="TYPE"
>double precision</TT
>,
        <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
>convert real/double precision to string</TD
><TD
><TT
CLASS="LITERAL"
>to_char(125.8::real, '999D9')</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_char</CODE
>(<TT
CLASS="TYPE"
>numeric</TT
>, <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
>convert numeric to string</TD
><TD
><TT
CLASS="LITERAL"
>to_char(-125.8, '999D99S')</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_date</CODE
>(<TT
CLASS="TYPE"
>text</TT
>, <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>date</TT
></TD
><TD
>convert string to date</TD
><TD
><TT
CLASS="LITERAL"
>to_date('05&nbsp;Dec&nbsp;2000', 'DD&nbsp;Mon&nbsp;YYYY')</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_number</CODE
>(<TT
CLASS="TYPE"
>text</TT
>, <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>numeric</TT
></TD
><TD
>convert string to numeric</TD
><TD
><TT
CLASS="LITERAL"
>to_number('12,454.8-', '99G999D9S')</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_timestamp</CODE
>(<TT
CLASS="TYPE"
>text</TT
>, <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>timestamp with time zone</TT
></TD
><TD
>convert string to time stamp</TD
><TD
><TT
CLASS="LITERAL"
>to_timestamp('05&nbsp;Dec&nbsp;2000', 'DD&nbsp;Mon&nbsp;YYYY')</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_timestamp</CODE
>(<TT
CLASS="TYPE"
>double precision</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>timestamp with time zone</TT
></TD
><TD
>convert Unix epoch to time stamp</TD
><TD
><TT
CLASS="LITERAL"
>to_timestamp(1284352323)</TT
></TD
></TR
></TBODY
></TABLE
></DIV
><P
>    In a <CODE
CLASS="FUNCTION"
>to_char</CODE
> output template string, there are certain
    patterns that are recognized and replaced with appropriately-formatted
    data based on the given value.  Any text that is not a template pattern is
    simply copied verbatim.  Similarly, in an input template string (for the
    other functions), template patterns identify the values to be supplied by
    the input data string.
   </P
><P
>   <A
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE"
>Table 9-21</A
> shows the
   template patterns available for formatting date and time values.
  </P
><DIV
CLASS="TABLE"
><A
NAME="FUNCTIONS-FORMATTING-DATETIME-TABLE"
></A
><P
><B
>Table 9-21. Template Patterns for Date/Time Formatting</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><THEAD
><TR
><TH
>Pattern</TH
><TH
>Description</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
>HH</TT
></TD
><TD
>hour of day (01-12)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>HH12</TT
></TD
><TD
>hour of day (01-12)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>HH24</TT
></TD
><TD
>hour of day (00-23)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>MI</TT
></TD
><TD
>minute (00-59)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>SS</TT
></TD
><TD
>second (00-59)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>MS</TT
></TD
><TD
>millisecond (000-999)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>US</TT
></TD
><TD
>microsecond (000000-999999)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>SSSS</TT
></TD
><TD
>seconds past midnight (0-86399)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>AM</TT
>, <TT
CLASS="LITERAL"
>am</TT
>,
        <TT
CLASS="LITERAL"
>PM</TT
> or <TT
CLASS="LITERAL"
>pm</TT
></TD
><TD
>meridiem indicator (without periods)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>A.M.</TT
>, <TT
CLASS="LITERAL"
>a.m.</TT
>,
        <TT
CLASS="LITERAL"
>P.M.</TT
> or <TT
CLASS="LITERAL"
>p.m.</TT
></TD
><TD
>meridiem indicator (with periods)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>Y,YYY</TT
></TD
><TD
>year (4 and more digits) with comma</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>YYYY</TT
></TD
><TD
>year (4 and more digits)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>YYY</TT
></TD
><TD
>last 3 digits of year</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>YY</TT
></TD
><TD
>last 2 digits of year</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>Y</TT
></TD
><TD
>last digit of year</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>IYYY</TT
></TD
><TD
>ISO year (4 and more digits)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>IYY</TT
></TD
><TD
>last 3 digits of ISO year</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>IY</TT
></TD
><TD
>last 2 digits of ISO year</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>I</TT
></TD
><TD
>last digit of ISO year</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>BC</TT
>, <TT
CLASS="LITERAL"
>bc</TT
>,
        <TT
CLASS="LITERAL"
>AD</TT
> or <TT
CLASS="LITERAL"
>ad</TT
></TD
><TD
>era indicator (without periods)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>B.C.</TT
>, <TT
CLASS="LITERAL"
>b.c.</TT
>,
        <TT
CLASS="LITERAL"
>A.D.</TT
> or <TT
CLASS="LITERAL"
>a.d.</TT
></TD
><TD
>era indicator (with periods)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>MONTH</TT
></TD
><TD
>full uppercase month name (blank-padded to 9 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>Month</TT
></TD
><TD
>full capitalized month name (blank-padded to 9 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>month</TT
></TD
><TD
>full lowercase month name (blank-padded to 9 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>MON</TT
></TD
><TD
>abbreviated uppercase month name (3 chars in English, localized lengths vary)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>Mon</TT
></TD
><TD
>abbreviated capitalized month name (3 chars in English, localized lengths vary)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>mon</TT
></TD
><TD
>abbreviated lowercase month name (3 chars in English, localized lengths vary)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>MM</TT
></TD
><TD
>month number (01-12)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>DAY</TT
></TD
><TD
>full uppercase day name (blank-padded to 9 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>Day</TT
></TD
><TD
>full capitalized day name (blank-padded to 9 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>day</TT
></TD
><TD
>full lowercase day name (blank-padded to 9 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>DY</TT
></TD
><TD
>abbreviated uppercase day name (3 chars in English, localized lengths vary)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>Dy</TT
></TD
><TD
>abbreviated capitalized day name (3 chars in English, localized lengths vary)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>dy</TT
></TD
><TD
>abbreviated lowercase day name (3 chars in English, localized lengths vary)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>DDD</TT
></TD
><TD
>day of year (001-366)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>IDDD</TT
></TD
><TD
>ISO day of year (001-371; day 1 of the year is Monday of the first ISO week.)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>DD</TT
></TD
><TD
>day of month (01-31)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>D</TT
></TD
><TD
>day of the week, Sunday(<TT
CLASS="LITERAL"
>1</TT
>) to Saturday(<TT
CLASS="LITERAL"
>7</TT
>)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>ID</TT
></TD
><TD
>ISO day of the week, Monday(<TT
CLASS="LITERAL"
>1</TT
>) to Sunday(<TT
CLASS="LITERAL"
>7</TT
>)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>W</TT
></TD
><TD
>week of month (1-5) (The first week starts on the first day of the month.)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>WW</TT
></TD
><TD
>week number of year (1-53) (The first week starts on the first day of the year.)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>IW</TT
></TD
><TD
>ISO week number of year (01 - 53; the first Thursday of the new year is in week 1.)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>CC</TT
></TD
><TD
>century (2 digits) (The twenty-first century starts on 2001-01-01.)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>J</TT
></TD
><TD
>Julian Day (days since November 24, 4714 BC at midnight)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>Q</TT
></TD
><TD
>quarter</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>RM</TT
></TD
><TD
>month in uppercase Roman numerals (I-XII; I=January)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>rm</TT
></TD
><TD
>month in lowercase Roman numerals (i-xii; i=January)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>TZ</TT
></TD
><TD
>uppercase time-zone name</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>tz</TT
></TD
><TD
>lowercase time-zone name</TD
></TR
></TBODY
></TABLE
></DIV
><P
>    Modifiers can be applied to any template pattern to alter its
    behavior.  For example, <TT
CLASS="LITERAL"
>FMMonth</TT
>
    is the <TT
CLASS="LITERAL"
>Month</TT
> pattern with the
    <TT
CLASS="LITERAL"
>FM</TT
> modifier.
    <A
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE"
>Table 9-22</A
> shows the
    modifier patterns for date/time formatting.
   </P
><DIV
CLASS="TABLE"
><A
NAME="FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE"
></A
><P
><B
>Table 9-22. Template Pattern Modifiers for Date/Time Formatting</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><THEAD
><TR
><TH
>Modifier</TH
><TH
>Description</TH
><TH
>Example</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
>FM</TT
> prefix</TD
><TD
>fill mode (suppress padding blanks and zeroes)</TD
><TD
><TT
CLASS="LITERAL"
>FMMonth</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>TH</TT
> suffix</TD
><TD
>uppercase ordinal number suffix</TD
><TD
><TT
CLASS="LITERAL"
>DDTH</TT
>, e.g., <TT
CLASS="LITERAL"
>12TH</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>th</TT
> suffix</TD
><TD
>lowercase ordinal number suffix</TD
><TD
><TT
CLASS="LITERAL"
>DDth</TT
>, e.g., <TT
CLASS="LITERAL"
>12th</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>FX</TT
> prefix</TD
><TD
>fixed format global option (see usage notes)</TD
><TD
><TT
CLASS="LITERAL"
>FX&nbsp;Month&nbsp;DD&nbsp;Day</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>TM</TT
> prefix</TD
><TD
>translation mode (print localized day and month names based on
         <A
HREF="runtime-config-client.html#GUC-LC-TIME"
>lc_time</A
>)</TD
><TD
><TT
CLASS="LITERAL"
>TMMonth</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>SP</TT
> suffix</TD
><TD
>spell mode (not implemented)</TD
><TD
><TT
CLASS="LITERAL"
>DDSP</TT
></TD
></TR
></TBODY
></TABLE
></DIV
><P
>    Usage notes for date/time formatting:

    <P
></P
></P><UL
><LI
><P
>       <TT
CLASS="LITERAL"
>FM</TT
> suppresses leading zeroes and trailing blanks
       that would otherwise be added to make the output of a pattern be
       fixed-width.
      </P
></LI
><LI
><P
>       <TT
CLASS="LITERAL"
>TM</TT
> does not include trailing blanks.
      </P
></LI
><LI
><P
>       <CODE
CLASS="FUNCTION"
>to_timestamp</CODE
> and <CODE
CLASS="FUNCTION"
>to_date</CODE
>
       skip multiple blank spaces in the input string unless the
       <TT
CLASS="LITERAL"
>FX</TT
> option is used. For example,
       <TT
CLASS="LITERAL"
>to_timestamp('2000&nbsp;&nbsp;&nbsp;&nbsp;JUN', 'YYYY MON')</TT
> works, but
       <TT
CLASS="LITERAL"
>to_timestamp('2000&nbsp;&nbsp;&nbsp;&nbsp;JUN', 'FXYYYY MON')</TT
> returns an error
       because <CODE
CLASS="FUNCTION"
>to_timestamp</CODE
> expects one space only.
       <TT
CLASS="LITERAL"
>FX</TT
> must be specified as the first item in
       the template.
      </P
></LI
><LI
><P
>       Ordinary text is allowed in <CODE
CLASS="FUNCTION"
>to_char</CODE
>
       templates and will be output literally.  You can put a substring
       in double quotes to force it to be interpreted as literal text
       even if it contains pattern key words.  For example, in
       <TT
CLASS="LITERAL"
>'"Hello Year "YYYY'</TT
>, the <TT
CLASS="LITERAL"
>YYYY</TT
>
       will be replaced by the year data, but the single <TT
CLASS="LITERAL"
>Y</TT
> in <TT
CLASS="LITERAL"
>Year</TT
>
       will not be.
      </P
></LI
><LI
><P
>       If you want to have a double quote in the output you must
       precede it with a backslash, for example <TT
CLASS="LITERAL"
>E'\\"YYYY
       Month\\"'</TT
>. 
       (Two backslashes are necessary because the backslash
       has special meaning when using the escape string syntax.)
      </P
></LI
><LI
><P
>       The <TT
CLASS="LITERAL"
>YYYY</TT
> conversion from string to <TT
CLASS="TYPE"
>timestamp</TT
> or
       <TT
CLASS="TYPE"
>date</TT
> has a restriction when processing years with more than 4 digits. You must
       use some non-digit character or template after <TT
CLASS="LITERAL"
>YYYY</TT
>,
       otherwise the year is always interpreted as 4 digits. For example
       (with the year 20000):
       <TT
CLASS="LITERAL"
>to_date('200001131', 'YYYYMMDD')</TT
> will be 
       interpreted as a 4-digit year; instead use a non-digit 
       separator after the year, like
       <TT
CLASS="LITERAL"
>to_date('20000-1131', 'YYYY-MMDD')</TT
> or
       <TT
CLASS="LITERAL"
>to_date('20000Nov31', 'YYYYMonDD')</TT
>.
      </P
></LI
><LI
><P
>       In conversions from string to <TT
CLASS="TYPE"
>timestamp</TT
> or
       <TT
CLASS="TYPE"
>date</TT
>, the <TT
CLASS="LITERAL"
>CC</TT
> (century) field is ignored
       if there is a <TT
CLASS="LITERAL"
>YYY</TT
>, <TT
CLASS="LITERAL"
>YYYY</TT
> or
       <TT
CLASS="LITERAL"
>Y,YYY</TT
> field. If <TT
CLASS="LITERAL"
>CC</TT
> is used with
       <TT
CLASS="LITERAL"
>YY</TT
> or <TT
CLASS="LITERAL"
>Y</TT
> then the year is computed
       as <TT
CLASS="LITERAL"
>(CC-1)*100+YY</TT
>.
      </P
></LI
><LI
><P
>       An ISO week date (as distinct from a Gregorian date) can be
       specified to <CODE
CLASS="FUNCTION"
>to_timestamp</CODE
> and
       <CODE
CLASS="FUNCTION"
>to_date</CODE
> in one of two ways:
       <P
></P
></P><UL
><LI
><P
>          Year, week, and weekday:  for example <TT
CLASS="LITERAL"
>to_date('2006-42-4',
          'IYYY-IW-ID')</TT
> returns the date
          <TT
CLASS="LITERAL"
>2006-10-19</TT
>.  If you omit the weekday it
          is assumed to be 1 (Monday).
         </P
></LI
><LI
><P
>          Year and day of year:  for example <TT
CLASS="LITERAL"
>to_date('2006-291',
          'IYYY-IDDD')</TT
> also returns <TT
CLASS="LITERAL"
>2006-10-19</TT
>.
         </P
></LI
></UL
><P>
      </P
><P
>       Attempting to construct a date using a mixture of ISO week and
       Gregorian date fields is nonsensical, and will cause an error.  In the
       context of an ISO year, the concept of a <SPAN
CLASS="QUOTE"
>"month"</SPAN
> or <SPAN
CLASS="QUOTE"
>"day
       of month"</SPAN
> has no meaning.  In the context of a Gregorian year, the
       ISO week has no meaning.  Users should avoid mixing Gregorian and
       ISO date specifications.
      </P
></LI
><LI
><P
>       In a conversion from string to <TT
CLASS="TYPE"
>timestamp</TT
>, millisecond
       (<TT
CLASS="LITERAL"
>MS</TT
>) or microsecond (<TT
CLASS="LITERAL"
>US</TT
>)
       values are used as the
       seconds digits after the decimal point. For example 
       <TT
CLASS="LITERAL"
>to_timestamp('12:3', 'SS:MS')</TT
> is not 3 milliseconds,
       but 300, because the conversion counts it as 12 + 0.3 seconds.
       This means for the format <TT
CLASS="LITERAL"
>SS:MS</TT
>, the input values
       <TT
CLASS="LITERAL"
>12:3</TT
>, <TT
CLASS="LITERAL"
>12:30</TT
>, and <TT
CLASS="LITERAL"
>12:300</TT
> specify the
       same number of milliseconds. To get three milliseconds, one must use
       <TT
CLASS="LITERAL"
>12:003</TT
>, which the conversion counts as
       12 + 0.003 = 12.003 seconds.
      </P
><P
>       Here is a more 
       complex example: 
       <TT
CLASS="LITERAL"
>to_timestamp('15:12:02.020.001230', 'HH:MI:SS.MS.US')</TT
>
       is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds +
       1230 microseconds = 2.021230 seconds. 
      </P
></LI
><LI
><P
>        <CODE
CLASS="FUNCTION"
>to_char(..., 'ID')</CODE
>'s day of the week numbering
        matches the <CODE
CLASS="FUNCTION"
>extract(isodow from ...)</CODE
> function, but
        <CODE
CLASS="FUNCTION"
>to_char(..., 'D')</CODE
>'s does not match
        <CODE
CLASS="FUNCTION"
>extract(dow from ...)</CODE
>'s day numbering.
      </P
></LI
><LI
><P
>        <CODE
CLASS="FUNCTION"
>to_char(interval)</CODE
> formats <TT
CLASS="LITERAL"
>HH</TT
> and
        <TT
CLASS="LITERAL"
>HH12</TT
> as hours in a single day, while <TT
CLASS="LITERAL"
>HH24</TT
>
        can output hours exceeding a single day, e.g., &gt;24.
      </P
></LI
></UL
><P>
   </P
><P
>   <A
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-NUMERIC-TABLE"
>Table 9-23</A
> shows the
   template patterns available for formatting numeric values.
  </P
><DIV
CLASS="TABLE"
><A
NAME="FUNCTIONS-FORMATTING-NUMERIC-TABLE"
></A
><P
><B
>Table 9-23. Template Patterns for Numeric Formatting</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><THEAD
><TR
><TH
>Pattern</TH
><TH
>Description</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
>9</TT
></TD
><TD
>value with the specified number of digits</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>0</TT
></TD
><TD
>value with leading zeros</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>.</TT
> (period)</TD
><TD
>decimal point</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>,</TT
> (comma)</TD
><TD
>group (thousand) separator</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>PR</TT
></TD
><TD
>negative value in angle brackets</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>S</TT
></TD
><TD
>sign anchored to number (uses locale)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>L</TT
></TD
><TD
>currency symbol (uses locale)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>D</TT
></TD
><TD
>decimal point (uses locale)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>G</TT
></TD
><TD
>group separator (uses locale)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>MI</TT
></TD
><TD
>minus sign in specified position (if number &lt; 0)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>PL</TT
></TD
><TD
>plus sign in specified position (if number &gt; 0)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>SG</TT
></TD
><TD
>plus/minus sign in specified position</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>RN</TT
></TD
><TD
>Roman numeral (input between 1 and 3999)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>TH</TT
> or <TT
CLASS="LITERAL"
>th</TT
></TD
><TD
>ordinal number suffix</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>V</TT
></TD
><TD
>shift specified number of digits (see notes)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>EEEE</TT
></TD
><TD
>scientific notation (not implemented)</TD
></TR
></TBODY
></TABLE
></DIV
><P
>    Usage notes for numeric formatting:

    <P
></P
></P><UL
><LI
><P
>       A sign formatted using <TT
CLASS="LITERAL"
>SG</TT
>, <TT
CLASS="LITERAL"
>PL</TT
>, or
       <TT
CLASS="LITERAL"
>MI</TT
> is not anchored to
       the number; for example,
       <TT
CLASS="LITERAL"
>to_char(-12, 'MI9999')</TT
> produces <TT
CLASS="LITERAL"
>'-&nbsp;&nbsp;12'</TT
>
       but <TT
CLASS="LITERAL"
>to_char(-12, 'S9999')</TT
> produces <TT
CLASS="LITERAL"
>'&nbsp;&nbsp;-12'</TT
>.
       The Oracle implementation does not allow the use of
       <TT
CLASS="LITERAL"
>MI</TT
> before <TT
CLASS="LITERAL"
>9</TT
>, but rather
       requires that <TT
CLASS="LITERAL"
>9</TT
> precede
       <TT
CLASS="LITERAL"
>MI</TT
>.
      </P
></LI
><LI
><P
>       <TT
CLASS="LITERAL"
>9</TT
> results in a value with the same number of 
       digits as there are <TT
CLASS="LITERAL"
>9</TT
>s. If a digit is
       not available it outputs a space.
      </P
></LI
><LI
><P
>       <TT
CLASS="LITERAL"
>TH</TT
> does not convert values less than zero
       and does not convert fractional numbers.
      </P
></LI
><LI
><P
>       <TT
CLASS="LITERAL"
>PL</TT
>, <TT
CLASS="LITERAL"
>SG</TT
>, and
       <TT
CLASS="LITERAL"
>TH</TT
> are <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
       extensions. 
      </P
></LI
><LI
><P
>       <TT
CLASS="LITERAL"
>V</TT
> effectively
       multiplies the input values by
       <TT
CLASS="LITERAL"
>10^<TT
CLASS="REPLACEABLE"
><I
>n</I
></TT
></TT
>, where
       <TT
CLASS="REPLACEABLE"
><I
>n</I
></TT
> is the number of digits following
       <TT
CLASS="LITERAL"
>V</TT
>.
       <CODE
CLASS="FUNCTION"
>to_char</CODE
> does not support the use of
       <TT
CLASS="LITERAL"
>V</TT
> combined with a decimal point
       (e.g., <TT
CLASS="LITERAL"
>99.9V99</TT
> is not allowed).
      </P
></LI
></UL
><P>
   </P
><P
>    Certain modifiers can be applied to any template pattern to alter its
    behavior.  For example, <TT
CLASS="LITERAL"
>FM9999</TT
>
    is the <TT
CLASS="LITERAL"
>9999</TT
> pattern with the
    <TT
CLASS="LITERAL"
>FM</TT
> modifier.
    <A
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-NUMERICMOD-TABLE"
>Table 9-24</A
> shows the
    modifier patterns for numeric formatting.
   </P
><DIV
CLASS="TABLE"
><A
NAME="FUNCTIONS-FORMATTING-NUMERICMOD-TABLE"
></A
><P
><B
>Table 9-24. Template Pattern Modifiers for Numeric Formatting</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><THEAD
><TR
><TH
>Modifier</TH
><TH
>Description</TH
><TH
>Example</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
>FM</TT
> prefix</TD
><TD
>fill mode (suppress padding blanks and zeroes)</TD
><TD
><TT
CLASS="LITERAL"
>FM9999</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>TH</TT
> suffix</TD
><TD
>uppercase ordinal number suffix</TD
><TD
><TT
CLASS="LITERAL"
>999TH</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>th</TT
> suffix</TD
><TD
>lowercase ordinal number suffix</TD
><TD
><TT
CLASS="LITERAL"
>999th</TT
></TD
></TR
></TBODY
></TABLE
></DIV
><P
>   <A
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-EXAMPLES-TABLE"
>Table 9-25</A
> shows some
   examples of the use of the <CODE
CLASS="FUNCTION"
>to_char</CODE
> function.
  </P
><DIV
CLASS="TABLE"
><A
NAME="FUNCTIONS-FORMATTING-EXAMPLES-TABLE"
></A
><P
><B
>Table 9-25. <CODE
CLASS="FUNCTION"
>to_char</CODE
> Examples</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><THEAD
><TR
><TH
>Expression</TH
><TH
>Result</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(current_timestamp, 'Day,&nbsp;DD&nbsp;&nbsp;HH12:MI:SS')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'Tuesday&nbsp;&nbsp;,&nbsp;06&nbsp;&nbsp;05:39:18'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(current_timestamp, 'FMDay,&nbsp;FMDD&nbsp;&nbsp;HH12:MI:SS')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'Tuesday,&nbsp;6&nbsp;&nbsp;05:39:18'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-0.1, '99.99')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;&nbsp;-.10'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-0.1, 'FM9.99')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'-.1'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(0.1, '0.9')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;0.1'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(12, '9990999.9')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;&nbsp;&nbsp;&nbsp;0012.0'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(12, 'FM9990999.9')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'0012.'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, '999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-485, '999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'-485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, '9&nbsp;9&nbsp;9')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;4&nbsp;8&nbsp;5'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(1485, '9,999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;1,485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(1485, '9G999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;1&nbsp;485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(148.5, '999.999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;148.500'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(148.5, 'FM999.999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'148.5'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(148.5, 'FM999.990')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'148.500'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(148.5, '999D999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;148,500'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(3148.5, '9G999D999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;3&nbsp;148,500'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-485, '999S')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'485-'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-485, '999MI')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'485-'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, '999MI')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'485&nbsp;'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, 'FM999MI')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, 'PL999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'+485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, 'SG999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'+485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-485, 'SG999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'-485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-485, '9SG99')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'4-85'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-485, '999PR')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&lt;485&gt;'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, 'L999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'DM&nbsp;485</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, 'RN')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CDLXXXV'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, 'FMRN')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'CDLXXXV'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(5.2, 'FMRN')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'V'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(482, '999th')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;482nd'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, '"Good&nbsp;number:"999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'Good&nbsp;number:&nbsp;485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485.8, '"Pre:"999"&nbsp;Post:"&nbsp;.999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'Pre:&nbsp;485&nbsp;Post:&nbsp;.800'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(12, '99V999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;12000'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(12.4, '99V999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;12400'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(12.45, '99V9')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;125'</TT
></TD
></TR
></TBODY
></TABLE
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions-matching.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions-datetime.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Pattern Matching</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="functions.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Date/Time Functions and Operators</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>