Oracle to_char Formatting Function

Postgres Formatting Function provides a set of effective tools for various data types (date/time, int, float, numeric) convert to formatted string, and convert from formatted string to original data type in turn. Note: The second parameter of all formatting functions is the template used for conversion. Table 5-7. format function return Description Example to_char (timestamp, text) text converts timestamp to string to_char (timestamp 'right', 'hh12: MI: ss') www.2cto.com to_char (int, text) text converts int4/int8 to string to_char (125, '000000') to_char (float, text) text converts float4/float8 to string to_char (999, '999d9 ') to_char (numeric, text) text converts numeric to string to_char (numeric '-125.8', '999d99s') to_date (text, text) date converts string to date to_d Ate ('05 Dec 2000 ', 'dd Mon yyyy') to_timestamp (text, text) date converts string to timestamp to_timestamp ('05 Dec 2000', 'dd Mon yyyy ') to_number (text, text) numeric converts string to numeric to_number ('2017-', '99g999d9s') Table 5-8. the template used for date/time conversion describes the hour of HH in a day (01-12) hour of HH12 in a day (01-12) hour of HH24 in a day (00-23) MI minute (00-59) SS second (00-59) SSSS midnight second (0-86399) AM or A.M. or PM or P. m. midday logo (uppercase) am or a.m. or pm or p. m. Midday logo (lower case) Y, YYY year with comma (4 and more) www.2cto.com YYYY (4 and more) the last BC or B .C. or AD or A.D. year ID (uppercase) bc or B .c. or ad or a.d. year ID (lower case) MONTH full-length capital Month name (9 characters) month full-length mixed case MONTH name (9 characters) Month full-length lowercase month name (9 characters) MON capital abbreviation month name (3 characters) Mon abbreviation mixed case month name (3 characters) mon lowercase abbreviation month name (3 characters) MM Month (01-12) DAY full-length capital Date name (9 characters) Day full-length mixed-case Date name (9 characters) day full-length lowercase Date name (9 characters) DY abbreviation capital Date name (3 characters) dy Abbreviation: mixed case Date name (3 characters) dy abbreviation lowercase Date name (3 characters) DDD day of the year (001-366) DD day of the month (01-31) D days in a week (1-7; SUN = 1) W weeks in a month WW weeks in a year CC Century (2) J Julian Date (date since January 1, 4712 BC) Q quarter RM month of the Roman numerals (I-XII; I = JAN)-month of the uppercase rm roman numerals (I-XII; I = JAN) -All lower-case templates allow the use of prefix and suffix modifiers. The modifier is always allowed in the template. The prefix 'fx 'is just a global modifier.
Table 5-9. suffix used for the date/time template to_char () Description Example: FM fill mode prefix FMMonthTH Capital order number suffix DDTHth lowercase Order Number suffix DDTHFX fixed mode Global Options (see below) FX Month DD DaySP spelling mode (not implemented yet) DDSP www.2cto.com Usage Note: If the FX option is not used, to_timestamp and to_date are ignored. FX must be the first entry declaration in the template. The backslash ("\") must be used as a double backslash ("\"), for example, '\ HH \ MI \ SS '. Strings between double quotation marks ('"') are ignored and not analyzed. To write double quotation marks to the output, you must place a double backslash ('\') before the double quotation marks, for example, '\ "YYYY Month \\"'. To_char supports text without leading double quotation marks ('"'), but any strings between double quotation marks will be processed quickly and will not be interpreted as template keywords (for example: '"Hello Year:" YYYY '). Table 5-10. to_char (numeric) template description 9 values with a specified number of digits 0 leading zero. (Period) decimal point, (comma) Grouping (thousands) Separator in PR angle brackets negative value S with negative value (using localization) L currency symbol (using localization) D decimal point (using localization) G grouping separator (using localization) the negative number of MI at the specified position (if the number is <0) The positive number of PL at the specified position (if the number is greater than 0) SG converts TH or th in the specified position to a sequence number V moves n digits (decimal places) (see annotations) EEEE scientific notation. Not Supported now. Usage instructions: The signed characters of 'sg ', 'pl', or 'mi 'are not attached to a number. For example, to_char (-12, 's999999 ') generate '-12', while to_char (-12, 'mi999999') generates '-12 '. The implementation in Oracle cannot use MI before 9, but requires that 9 be before MI. Www.2cto.com PL, SG, and TH are Postgres extensions. 9 indicates the same number of digits as the 9 string. If no number is available, use a blank space ). TH does not convert values smaller than zero or decimal places. TH is an ipvs extension. V is convenient to multiply the input value by 10 ^ n, where n is the number following V. To_char does not support binding V with a decimal point (for example,. "99.9V99" is not allowed ). Table 5-11. to_char example Input and Output to_char (now (), 'day, HH12: MI: ss') 'tuesday, 05:39:18 'to _ char (now (), 'fmday, HH12: MI: SS ') 'tuesday, 05:39:18' to _ char (-0.1, '99. 99 ')'-. 10' to _ char (-0.1, 'fm9. 99 ')'-. 1 'to _ char (0.1, '0. 9 ') '000000' to _ char (12, '2017. 9 ') '000000' to _ char (12, 'fm9990999. 9 ') '000000' to _ char (0012, '000000') '000000' to _ char (-485, '000000')'-100' to _ char (999, '9 9 9') '4 8 5' to _ char (1485, '000000') '000000' to _ char (9,999, '9g999 ') '1 100' to _ char (485, '123. 999 ') '000000' to _ char (148.500, '999d999') '000000' to _ char (148.5, '9g999d999') '3 148,500' to _ char (-3148.5, '999s') '2017-'www.2cto.com to_char (-485, '999mi') '2017-'to _ char (485, '999mi ') '000000' to _ char (485, 'pl999') '+ 100' to _ char (485, 'sg999')' + 100' to _ char (-485, 'sg999') '-485' to _ char (-485, '9sg99') '4-85 'to _ char (-485, '999pr ') '<485>' to _ char (485, 'l999') 'dm 485to_char (485, 'rn ')' CDLXXXV 'to _ char (485, 'fmrn ') 'cdlxxxv 'to _ char (5.2, 'fmrn') Vto_char (482, '999th') '482nd' to _ char (485, '"Good number:" 999 ') 'Good number: 100' to _ char (485, '"Pre-decimal:" 485.8 "Post-decimal :". 999 ') 'Pre-decimal: 485 Post-decimal :. 800 'to _ char (12, '99v999') '000000' to _ char (12000, '99v999') '000000' to _ char (12.4, '99v9 ') '20140901' from a leisurely BLOG