<!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.0.11 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="2007-02-02T03:57:22"></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.0.11 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="AEN9549" ></A ><A NAME="AEN9551" ></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 ><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 2m 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 Dec 2000', 'DD Mon YYYY')</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 Dec 2000', 'DD Mon 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 ></TBODY ></TABLE ></DIV ><P > Warning: <TT CLASS="LITERAL" ><CODE CLASS="FUNCTION" >to_char</CODE >(<TT CLASS="TYPE" >interval</TT >, <TT CLASS="TYPE" >text</TT >)</TT > is deprecated and should not be used in newly-written code. It will be removed in the next version. </P ><P > In an output template string (for <CODE CLASS="FUNCTION" >to_char</CODE >), there are certain patterns that are recognized and replaced with appropriately-formatted data from the value to be formatted. Any text that is not a template pattern is simply copied verbatim. Similarly, in an input template string (for anything but <CODE CLASS="FUNCTION" >to_char</CODE >), template patterns identify the parts of the input data string to be looked at and the values to be found there. </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 > or <TT CLASS="LITERAL" >A.M.</TT > or <TT CLASS="LITERAL" >PM</TT > or <TT CLASS="LITERAL" >P.M.</TT ></TD ><TD >meridian indicator (uppercase)</TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >am</TT > or <TT CLASS="LITERAL" >a.m.</TT > or <TT CLASS="LITERAL" >pm</TT > or <TT CLASS="LITERAL" >p.m.</TT ></TD ><TD >meridian indicator (lowercase)</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 digits of ISO year</TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >BC</TT > or <TT CLASS="LITERAL" >B.C.</TT > or <TT CLASS="LITERAL" >AD</TT > or <TT CLASS="LITERAL" >A.D.</TT ></TD ><TD >era indicator (uppercase)</TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >bc</TT > or <TT CLASS="LITERAL" >b.c.</TT > or <TT CLASS="LITERAL" >ad</TT > or <TT CLASS="LITERAL" >a.d.</TT ></TD ><TD >era indicator (lowercase)</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 mixed-case 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)</TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >Mon</TT ></TD ><TD >abbreviated mixed-case month name (3 chars)</TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >mon</TT ></TD ><TD >abbreviated lowercase month name (3 chars)</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 mixed-case 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)</TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >Dy</TT ></TD ><TD >abbreviated mixed-case day name (3 chars)</TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >dy</TT ></TD ><TD >abbreviated lowercase day name (3 chars)</TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >DDD</TT ></TD ><TD >day of year (001-366)</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 week (1-7; Sunday is 1)</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 (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)</TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >J</TT ></TD ><TD >Julian Day (days since January 1, 4712 BC)</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 Roman numerals (I-XII; I=January) (uppercase)</TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >rm</TT ></TD ><TD >month in Roman numerals (i-xii; i=January) (lowercase)</TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >TZ</TT ></TD ><TD >time-zone name (uppercase)</TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >tz</TT ></TD ><TD >time-zone name (lowercase)</TD ></TR ></TBODY ></TABLE ></DIV ><P > Certain modifiers may 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 ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >th</TT > suffix</TD ><TD >lowercase ordinal number suffix</TD ><TD ><TT CLASS="LITERAL" >DDth</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 Month DD Day</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >SP</TT > suffix</TD ><TD >spell mode (not yet 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 > <CODE CLASS="FUNCTION" >to_timestamp</CODE > and <CODE CLASS="FUNCTION" >to_date</CODE > skip multiple blank spaces in the input string if the <TT CLASS="LITERAL" >FX</TT > option is not used. <TT CLASS="LITERAL" >FX</TT > must be specified as the first item in the template. For example <TT CLASS="LITERAL" >to_timestamp('2000 JUN', 'YYYY MON')</TT > is correct, but <TT CLASS="LITERAL" >to_timestamp('2000 JUN', 'FXYYYY MON')</TT > returns an error, because <CODE CLASS="FUNCTION" >to_timestamp</CODE > expects one space only. </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" >'\\"YYYY Month\\"'</TT >. (Two backslashes are necessary because the backslash already has a special meaning in a string constant.) </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 if you use a year 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 > 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 > Millisecond (<TT CLASS="LITERAL" >MS</TT >) and microsecond (<TT CLASS="LITERAL" >US</TT >) values in a conversion from string to <TT CLASS="TYPE" >timestamp</TT > are used as part of the seconds 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</CODE >'s day of the week numbering (see the 'D' formatting pattern) is different from that of the <CODE CLASS="FUNCTION" >extract</CODE > function. </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 < 0)</TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >PL</TT ></TD ><TD >plus sign in specified position (if number > 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 yet)</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, 'S9999')</TT > produces <TT CLASS="LITERAL" >' -12'</TT >, but <TT CLASS="LITERAL" >to_char(-12, 'MI9999')</TT > produces <TT CLASS="LITERAL" >'- 12'</TT >. The Oracle implementation does not allow the use of <TT CLASS="LITERAL" >MI</TT > ahead of <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 > <A HREF="functions-formatting.html#FUNCTIONS-FORMATTING-EXAMPLES-TABLE" >Table 9-24</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-24. <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, DD HH12:MI:SS')</TT ></TD ><TD ><TT CLASS="LITERAL" >'Tuesday , 06 05:39:18'</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS')</TT ></TD ><TD ><TT CLASS="LITERAL" >'Tuesday, 6 05:39:18'</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >to_char(-0.1, '99.99')</TT ></TD ><TD ><TT CLASS="LITERAL" >' -.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" >' 0.1'</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >to_char(12, '9990999.9')</TT ></TD ><TD ><TT CLASS="LITERAL" >' 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" >' 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 9 9')</TT ></TD ><TD ><TT CLASS="LITERAL" >' 4 8 5'</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >to_char(1485, '9,999')</TT ></TD ><TD ><TT CLASS="LITERAL" >' 1,485'</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >to_char(1485, '9G999')</TT ></TD ><TD ><TT CLASS="LITERAL" >' 1 485'</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >to_char(148.5, '999.999')</TT ></TD ><TD ><TT CLASS="LITERAL" >' 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" >' 148,500'</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >to_char(3148.5, '9G999D999')</TT ></TD ><TD ><TT CLASS="LITERAL" >' 3 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 '</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" >'<485>'</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >to_char(485, 'L999')</TT ></TD ><TD ><TT CLASS="LITERAL" >'DM 485</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >to_char(485, 'RN')</TT ></TD ><TD ><TT CLASS="LITERAL" >' 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" >' 482nd'</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >to_char(485, '"Good number:"999')</TT ></TD ><TD ><TT CLASS="LITERAL" >'Good number: 485'</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >to_char(485.8, '"Pre:"999" Post:" .999')</TT ></TD ><TD ><TT CLASS="LITERAL" >'Pre: 485 Post: .800'</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >to_char(12, '99V999')</TT ></TD ><TD ><TT CLASS="LITERAL" >' 12000'</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >to_char(12.4, '99V999')</TT ></TD ><TD ><TT CLASS="LITERAL" >' 12400'</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >to_char(12.45, '99V9')</TT ></TD ><TD ><TT CLASS="LITERAL" >' 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 >