To_char Summary of conversion functions for Oracle SQL functions

Source: Internet
Author: User
Tags julian day

To_char (X[[,C2],C3])
Feature converts a date or data to a char data type
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.
I int;
For I in 1..20 loop
Dbms_output.put_line (To_char (i, ' RN '));
End Loop;
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.
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

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)
4 Bc   Although the standard notation is B.C. (c lowercase) or BC, fortunately Oracle does not pay attention to this. means BC
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
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
46 YYY   A year that represents the 3,2,1 bit in the back, for example, 2008 can be evaluated as 008,08,8
  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

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:
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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.