To_char Summary of conversion functions for Oracle SQL functions

To_char (X[[,C2],C3])
Feature converts a date or data to a char data type
Parameters
X is a date or number data type.
C2 as format parameter
C3 Setting parameters for NLS
If x is the date nlsparm=nls_date_language controls the language that is used for the returned month and day.
If X is a numeric nlsparm=nls_numeric_characters used to specify the separator for the decimal and the kilobits, as well as the currency symbol.
nls_numeric_characters = "DG", nls_currency= "string"
"Return" VARCHAR2 character type
"Description 1" x is data type

C1 Format Table Reference:

Serial number	Format	Simple example	Description
1	, (comma)	' 9999,999 '	Commas, usually in the presence of a percentile, are used as a grouping symbol. If you want you can also be considered as a very bit, percentile appears, can appear n times, depending on the size of the number.
			The example of Metamorphosis is to_char (1234, ' 9,9,9,9 ').
			Note: Can only appear in the integer section.
2	. (Point number)	' 99.99 '	Dot, do not read as "period", the period is a circle, points can only appear in the decimal place. Only one time can occur.
			To_char (1234.34, ' 9,9,9,9.99 ')
			Note: Can only appear in one place, is the original data decimal place
3	$ (dollar sign)	' $999.99 '	The dollar. You can actually put it anywhere (under 10G)
			To_char (1234.34, ' 9,9,9,9.$99 ')
			Note: Only one occurrence can occur.
4	0 (0)	' 0999.99 '	0. Return the corresponding character in the corresponding position, or fill it with ' 0 ' if not.
			To_char (0.34, ' 9,9,9,0.$99 ') = ' $0.34 '; To_char (1234, ' 9999.00 ') = ' 1234.00 ';
			Note: This is a mandatory symbol, corresponding bit not, then fill with ' O ', which is 9 very different places
5	9	' 999.99 '	9. In decimal place, the conversion is converted to the corresponding character, if none is represented by 0;
			To_char (123, ' 999.99 ') = 123.00; To_char (123, ' 99999.9 ') = 123.0;
			Note: for 0 and 9, if the number of digits in the format is less than the number of digits, it will return ' # '.
			For example To_char (12345, ' 9999 ') = ' ##### '
6	B (whitespace)	' B999 '	There is no other special function, the integer part is preceded by a space, can appear in any position.
			' S ' | | To_char (1234, ' 99b99 ') = ' S 1234 ';
			Note: Can only appear in the integer area.
7	C (International currency symbol)	' C9999 '	Returns an ISO currency symbol at a specific location (the value represented by the nls_iso_currency parameter)
			To_char (1233, ' C9999 ') = ' CNY1234 ', this is the new international standard RMB, about which can be queried "international currency symbol"
			Note: Can only appear in the integer part of the first place.
			The settings for the current session can be modified by alter session set Nls_iso_currency= ' JAPAN ';
8	D (ISO decimal digit symbol)	' 999d99 '	This is the international version of the "Dot" (ISO), which is equivalent to the point number and can only occur once. The difference is that the database sets the content based on the parameter values of the Nls_numeric_character. The default value is the dot number.
			Note: There is no special need for this format symbol. Do not modify the parameter values easily.
			Alter Sesssion set can also be used to modify.
			Alter session set Nls_numeric_characters= '!, '; To_char (1234.34, ' 9999d99 ') =1234!34
9	EEEE (Scientific computing character)	9.9EEEE	Scientific Computing Symbols
			To_char (2008032001, ' 9.9EEEE ') = ' 2.01E+09 ', because it is a scientific method of calculation, so the decimal place is preceded by a 9 or 0, the number is meaningless.
10	G (group symbol)	999g999	is the ISO standard for commas (,), used as a grouping symbol, and can be used in multiple places.
			To_char (123456, ' 999g9g99 ') =123,4,56
			Note: The same as eighth-D, in addition, if you want to convert the decimal point, you want to use with D, not with the dot number.
11	L (local currency symbol)	' L999 '	is the local version of C. Can be placed at the front and back of the entire format.
			To_char (123456, '999g9g99D00L') =123,4,56.00￥
			Note: Same as item seventh C
12	MI (minus)	' 9999MI '	If it is negative, add a minus sign (-) to the tail, and if it is positive, the tail plus a space
			To_char (1234, ' 9999mi ') | | ' S ' | | To_char ( -5678, ' 9999MI ') =1234 s5678-
			Precautions: can only be placed in the format of the tail
13	PR (symbol)	9999PR	is another way of expressing negative numbers. If it is positive, add a space to the head, and if it is negative, enclose the number in small brackets <>.
			To_char ( -1234.89, ' 9g999d00pr ') =<1,234.89>
			Precautions: Same 12
14	RN (RN)	RN (RN)	Converts an integer (1-3999) to a Roman character. RN indicates uppercase, and RN is lowercase.
			Declare
			I int;
			Begin
			For I in 1..20 loop
			Dbms_output.put_line (To_char (i, ' RN '));
			End Loop;
			End
			Note: It can only be used on its own and cannot be combined with other symbols.
15	S	' 9999S '	It is a comprehensive and improved version of 12,13. Add a plus sign to an integer and a negative number to a symbol-. S is added in front, then in the rear.
			To_char ( -1234, ' S9999 ') =-1234; To_char (1234, ' S9999 ') =+1234
16	Tm	Tm9/tme	Use this parameter equal to no parameter To_char (number), should be ' tm9 ' is the default format parameter.
			To_char (1234, ' tme ') =1234
			Caveats: The format is either TM9 or TME.
			When the number is longer than 64 bits, the output of the TM9 is equivalent to the TME output.
17	U	U999	Dual currency symbols, such as the euro. function of the same 11 l
			To_char (999, ' U999 ') =￥999
			Precautions: Through Nls_dual_currency control
18	V	999v9	This is a strange, not very often used symbol. Its purpose is to make a calculation.
			For example, To_char (N, ' 999v9 '), where p represents the position of V, the expression =to_char (NX (10 of P-1)). However, 9 numbers must be guaranteed to be greater than or equal to the number of digits represented after the product.
			To_char (5, ' 9V ') =5*1=5;
			To_char (5, ' 9v9 ') =5*10=50
			To_char (5, ' 9v99 ') =500
			To_char (' 9v99 ') = ' ###### ' 9 is not enough
			Note: Formats cannot be written together with decimal expressions, but can be mixed with currency, and so on.
19	X	Xxxx	Convert to 16 binary.
			To_char (+, ' XX ') = 64
			Note: The value must be an integer greater than or equal to 0. The front can only be used in combination with 0 or FM.
20			In the example above, we have learned the format of various numbers. It can be said that the format is too diverse, difficult to remember in the brain, preferably as a reference exists.
			Classified:
			Numeric class : 0,9,
			Grouping class: (. ), (,), D,g, where points are good and commas are highlighted because they are not obvious, so use parentheses.
			Currency Category: $,c,l,u
			Calculation Transformation class : Eeee,rn,v,x
			Positive and negative symbols:mi,pr,s
			Other classes : B
			Orthodox class:TM

Example
To_char (1210.73, ' 9999.9 ') returns ' 1210.7 '
To_char (1210.73, ' 9,999.99 ') return ' 1,210.73 '
To_char (1210.73, ' $9,999.00 ') return ' $1,210.73 '
To_char (21, ' 000099 ') returns ' 000021 '
To_char (852, ' xxxx ') return ' 354 '


"Description 2" x is a date type, C2 available parameters
　

Serial number	Format	Simple example	Description
1	- / , . ; :	Slightly	The time separator symbol, in addition to the standard several, also allows the use of text as a segmentation symbol.
	"Text"		For example To_char (sysdate, ' YYYY ' year "MM" month "DD" day ") = April 24, 2008
2	AD		That is, the Latin anno Domini, the abbreviation for the ad. will be converted to AD, etc. according to the different NLS
	A.D.		No special precautions
3	Am		Morning shorthand, same as PM, P.M. (PM), Chinese environment output is morning (if it is morning)
	a.m..
4	Bc		Although the standard notation is B.C. (c lowercase) or BC, fortunately Oracle does not pay attention to this. means BC
	B.C.
5	Cc		Returned to the century, expressed in Arabic numerals
	Scc		If the last two bits of the year are between 01-99 then, return the first two bits +1, otherwise return the first two digits
6	D		One day of the week, the return is ordinal 1-7
7	Day		One of the days of the week, but the return of the day of the week, which is related to language settings, in the Chinese environment nls_date_language=simplified Chinese, from Monday to Sunday expressed
8	Dd		Day of the Month (1-31)
9	Ddd		One day in the year (1-366)
10	Dl	' DL '	Returns a long date format. Controlled by the Nls_territory,nls_language parameter. Example Monday, April 28, 2008
			Limitations: Other than DL, nothing else can be set.
11	Ds		Returns the short date format. Controlled by the Nls_territory,nls_language parameter. For example 2008-04-28
			Limitations: Other than DL, nothing else can be set.
12	Dy		The abbreviation of the date is the day of the week (of course this refers to the Chinese environment)
13	E		The era abbreviation, but only suitable for the following centralized calendar: Japanese royal family, Republic of China, too Buddhist calendar
14	Ee		The whole era, suitable for the situation with E
15	FF [1..9]		Is the millisecond, if not the number is the default precision.
			Can only be used with timestamp types.
16	Fm		One of the notable functions: no content is returned.
			I'm not sure why Oracle set this thing up.
17	Fx		Ditto
18	HH		Represents hours, 12-hour, with Hh12 (1-12)
19	HH12		Represents hours, 12-hour (1-12)
20	HH24		Represents hours, 24-hour (0-23)
21st	IW		ISO Standard week serial number (1-52, or 1-53)
22	Iyyy		Iyy,iy,i, ISO year (4-bit) 4,3,2,1 digits (reciprocal)
	Iyy		To_char (To_date (21120401, ' yyyymmdd '), ' iyyy, iyy,iy,i ') = 2112, 112,12,2
	IY
	I
23	J		Julian Day (more than one calendar for astronomy), from January 1, 4712 BC, the result is an integer, the algorithm is generally (A.D. date +4712) * Julian calendar average number of days
24	MI		Seconds (0-59)
25	Mm		2-month (1-12)
26	MON		The abbreviation of the month, and the national related department nls_date_language, for example 04 in Chinese environment with April expressed.
27	MONTH		The name of the month, the state related department Nls_date_language, currently in Chinese under 04 is expressed as April.
28	Pm		The same am,a.m that afternoon.
	the p.m..
29	Q		Quarter (1-4)
30	Rm		Month in Roman numerals, I, II, III, IV, V, VI, VII, Viii,ix, X, XI, XII
31	Rr		A little rounding represents the meaning of the year, and the specific usage is a little bit more complicated.
			The last two bits of the year entered in S, C represents the last two bits of the current year, and the output (the new first two bits of the year) can be represented by the function r=f (s,c), which S2,C2 represents the first two bits of s,c respectively.
			1) s=[0,49],c=[0,49], then R=C2
			2) s=[0,49],c=[50,99], then r=c2+1
			3) s=[50,99],c=[0,49], then r=c2-1
			4) s=[50,99],c=[50,99], then R=C2
			In short, it is close to the current year principle, if it is the same as the current year, if it is larger than the current one, then it is considered to be the current century ago First century, otherwise it is the next century.
			For example, take to_date as an example
			Sql> Select To_date (' 89-01-01 ', ' rr-mm-dd '), to_date (' 12-01-01 ', ' rr-mm-dd ') from DUAL;
			To_date (' 89-01-01 ', ' Rr-mm-dd ') to_date (' 12-01-01 ', ' rr-mm-dd ')
			------------------------------ ------------------------------
			1989-01-01 2012-01-01
			I think Oracle is going to get this stuff out, and it's estimated that two of them are for convenience and one is to deal with a century or a thousand-year problem.
32	RRRR		If the input parameter is only two bits, then the same RR, otherwise the same yyyy effect.
33	Ss		Seconds (0-59), within one minute
34	Sssss		The cumulative number of seconds from midnight of the day. (0-86399)
35	Ts		Returns the short date format content, including seconds, minutes, and so on, can only be used in combination with DL,DS, the format is:
			DL ts or DL ts, separated by a space interval. To_char (sysdate, ' TS ') = 4:50:04
			The expressions are influenced by nls_territory and Nls_language.
36	TZD		Daylight saving time information, timezone shorthand plus daylight saving time information, must correspond to the timezone set in format TZR.
			Including the following three TZ start, are related to the time zone, not directly used in To_char
37	TZH		Hours in a time zone, such as Hh:mi:ss.fftzh:tzm '
38	TZM		The minutes in the time zone.
39	TZR		Region information in the time zone, which must be the time zone supported by the database, such as Us/pacific
40	Ww		Similar to IW, it also represents the ordinal of a week, from the first day of the year to the last seventh day of the year. The values are basically the same. (1-53), for example, 2008-01-01 to 2008-01-07 Count 1,2008-01-09~2008-01-13 2
41	W		One months of the week ordinal, the algorithm with WW, but confined to the January only, and ISO differences.
42	X		Represents the local root symbol, which is not particularly useful and can only be used with the timestamp type.
43	Y,yyy		Four-bit years, separated by good example 2,008
44	Year		Year of articulation, e.g. 2008=two thousand eight
	Syear		s prefix means BC BC
45	YYYY		Four-digit year, s-prefix denotes BC
	Syyyy
46	YYY		A year that represents the 3,2,1 bit in the back, for example, 2008 can be evaluated as 008,08,8
	Yy
	Y
	Summarize		From the above, the main expression is the format of several parts of the time: Century, year, month, day, hour, minute, second, millisecond, and some other mixed format. Each time part can have a variety of expressions, through this classification is more easily remembered.
			Many formats can be used together, which can eventually form a sufficiently rich expression of their situation;
			Second, many formats and NLS are closely related, and finally some output (return) and the format of the case is related, this is not reflected in the Chinese environment (currently not seen), but the English environment under the name of To_char (sysdate, ' Day ') as an example, If the western environment is to return sun (assuming Sysdate is on the weekends), if To_char (sysdate, ' Day ') returns to Sun

Example
To_char (sysdate, ' d ') Day of the week
To_char (sysdate, ' DD ') Day of the month
To_char (sysdate, ' DDD ') the first few days of each year
To_char (sysdate, ' WW ') the first few weeks of each year
To_char (sysdate, ' mm ') the first few months of each year
To_char (sysdate, ' Q ') the first quarter of each year
To_char (sysdate, ' yyyy ') year

Sql> Select To_char (sysdate, ' PM yyyy-mm-dd hh24:mi:sssss AD year Mon day ddd iws ') from DUAL;
To_char (sysdate, ' Pmyyyy-mm-ddh
--------------------------------------------------------------------------------
Morning 2008-03-27 09:58:35917 A.D. thousand eight March Thu 087 13
Sql> SELECT to_char (Systimestamp, ' HH24:MI:SS. FF5 ') from DUAL;
To_char (Systimestamp, ' Hh24:mi:
------------------------------
10:02:28.90000
Sql>select to_char (sysdate, ' DS DL ') from DUAL
To_char (sysdate, ' DSDL ')
-----------------------------------
2008-03-27 March 27, 2008 Thursday

Example with C3 example

Select To_char (to_date (' 2002-08-26 ', ' yyyy-mm-dd '), ' Day ', ' nls_date_language = American ') from dual;
Return: Monday

To_char Summary of conversion functions for Oracle SQL functions