Oracle data type conversion to_char (date)

Oracle data type conversion
Oracle data type conversion includes explicit data type conversion and implicit data type conversion.

In general, implicit data type conversion is available, but we recommend that you convert the display data type to ensure the reliability of SQL.

Explicit data type conversion includes:

To_number character to number
To_date character to date
To_char numeric conversion to character
To_char date to to_char (date, 'format _ model ')
Must be enclosed in single quotes and case sensitive
Can contain any valid date format Element
There is a FM element used to delete the filled null, or the leading zero
Use a comma to separate the select employee_id, to_char (hire_date, 'Mm/yy') as month
From employees
Where last_name = 'higgin'

Employee_id month
205 06/94

Select to_char (sysdate, 'yyyy-mm-dd hh24: MI: SS am') as today from dual
January 13:39:06 select to_char (to_date ('21--95 '), 'yyyy-mm-dd hh24: MI: SS am') as today from dual
00:00:00 select to_char (sysdate, 'dd "of" month ') from dual
24 of June select to_char (syadate, 'yyyy-mm-dd CC') from dual
2006-06-24 21
Cc: century select to_char (sysdate, 'yyy-mm-dd CC') as today from dual
006-06-24 21

Select to_char (sysdate, 'yy-mm-dd CC') as today from dual
06-06-24 21

Select to_char (sysdate, 'y, YYY-mm-dd CC') as today from dual
2,006-06-24 21

 

 

 

The following are number examples for the to_char function.

To_char(1210.73, '192. 9 ')	Wocould return '192. 7'
To_char(1210.73, '123 ')	Wocould return '000000'
To_char(1210.73, '$9,999.00 ')	Wocould return '$1,210.73'
To_char(21, '123 ')	Wocould return '000000'

The following is a list of valid parameters whenTo_charFunction is used to convert a date to a string. These parameters can be used in parallel combinations.

Parameter	Explanation
Year	Year, spelled out
Yyyy	4-digit year
Yyy YY Y	Last 3, 2, or 1 digit (s) of year.
Iyy Iy I	Last 3, 2, or 1 digit (s) of ISO year.
Iyyy	4-digit year based on the ISO standard
Q	Quarter of year (1, 2, 3, 4; Jan-MAR = 1 ).
Mm	Month (01-12; Jan = 01 ).
Mon	Abbreviated name of month.
Month	Name of month, padded with blanks to length of 9 characters.
Rm	Roman numeral month (I-XII; Jan = I ).
WW	Week of year (1-53) Where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7 ).
Day	Name of day.
Dd	Day of month (1-31 ).
Ddd	Day of year (1-366 ).
Dy	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of Day (1-12 ).
Hh12	Hour of Day (1-12 ).
Hh24	Hour of day (0-23 ).
Mi	Minute (0-59 ).
SS	Second (0-59 ).
Sssss	Seconds past midnight (0-86399 ).
FF	Fractional seconds.

The following are date examples forTo_charFunction.

To_char(Sysdate, 'yyyy/MM/dd ');	Wocould return '2017/09'
To_char(Sysdate, 'month DD, yyyy ');	Wocould return 'july 09,200 3'
To_char(Sysdate, 'fmmonth DD, yyyy ');	Wocould return 'july 9, 100'
To_char(Sysdate, 'mon ddth, yyyy ');	Wocould return 'Jul 09th, 100'
To_char(Sysdate, 'fmmon ddth, yyyy ');	Wocould return 'Jul 9th, 100'
To_char(Sysdate, 'fmmon ddth, yyyy ');	Wocould return 'Jul 9th, 100'

You will notice that in some examples,Format_maskParameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.

To_char(Sysdate, 'fmmonth DD, yyyy ');	Wocould return 'july 9, 100'
To_char(Sysdate, 'fmmon ddth, yyyy ');	Wocould return 'Jul 9th, 100'
To_char(Sysdate, 'fmmon ddth, yyyy ');	Wocould return 'Jul 9th, 100'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09 ".

Use the Oracle function to_char to convert a number to a specified number of decimal places

To_charThe function converts a numeric or date type to a numeric type.

For example, the simplest application:

Select To_char(1.0123) from dual

SelectTo_char(123) from dual

Next let's take a look at the following:

Selec To_char(0.123) from dual

The above result '. 100' is not what we want in most cases. What we want is '0. 100 '.

Let's take a look.To_charFunction usage:

TO_CHAR ( n [, fmt [, 'nlsparam']] )

This function converts N of the number type into a value of the varchar2 type in the numeric format FMT. 'Nlsparams' specifies the characters returned by the element in numerical format, including:

. Decimal point character

. Group Separator

. Local coin symbol

. International coin symbol

The form of Yuan change is:

'Nls _ numeric_characters = "DG" nls_currency = "tcxt" nls_iso_currency = territory'

D indicates the decimal point, and G indicates the group separator.

Example:To_char(17145, 'l099g999', 'nls _ numeric_characters = ".," nls_currency = "NUD" ') = nud017, 145

Based on the above understanding, and then look at some FMT formats, we can use the following expression to get the value of '0. 123:

Select To_char(0.123, '0. 999 ') from dual

SelectTo_char(100.12, '0. 999 ') from dual

SelectTo_char(1.12, '0. 999 ') from dual

'123' is displayed, but there is a space in front.

The value of 100.12 is ######, and the value of '1. 12' is changed to '1. 120 '.

Let's re-determine a new requirement:

1. Remove Space

2. A maximum of four decimal places, with at least two digits retained.

1 ---> '1. 00'; 1.1 ---> '1. 00'; 1.12 --> '1. 12'; 1.1234 ---> '1. 100 ';

1.12345 ---> '1. 1235'

The final implementation is as follows:

Select To_char(123.0233, 'fm9999999. 0099 ') from dual

Small PLSQL Experience 1. OracleTo_char() Functions are powerful, but pay attention to the following items when formatting numeric data. If it is a decimal, such as: 0.23, the data passes throughTo_char<Strong onmouseover = "isshowads = true; isshowads2 = true; ads. move (this, "http://rad.17luntan.com/UploadImage/17/633111011619218750.gif ", "% u89c1 % u6240 % u672a % u89c1 % uff01 % u62a2 % capacity % u9510 % u6bd4 % u6db2 % capacity % u65e0 % u7ebf % u5927 % u793c % 21 ", "42887", "show", "% u663e % u793a % 20", "HTTP % 3A // 210.192.100.35/11779028996160 HTTP % 3A // timeout", event) "Style =" font- Weight: normal; cursor: hand; color: # 0000ff; text-Decoration: underline "onclick =" javascript: window. Open ("http://s0.17luntan.com/ClickPortal/WebClick.aspx? Id = 42887 & K = % u663e % u793a & siteid = 95d6d193-1fb9-4fc0-8708-b7ceb3276924 & url = http % 3A // timeout ");" onmouseout = "isshowads = false; isshowads2 = false "> the display will change. 23. If you want to display the original 0.23, you have to use it.To_char(Value to be formatted, '0. 999 ') Keep three decimal places and display them, but pay attention to them here. He rounded down the number of decimal places for you. Therefore, if you want to cut off decimals without rounding them, you should write a function to cut them down before normalization. To ensure that it is not rounded.

II,To_char(1.9999, 'fm90. 0999 ') when this function is normalized, 90.0999 indicates that 9 indicates that a number is displayed if no number is displayed, where there is 0, there will also be 0 to placeholder when there is no number. but this also has a major drawback, that is, if it is an integer, it will still display ". ", don't underestimate this point. Generally, this point is redundant if it is to be displayed on the page. it also caused us a lot of trouble. you need to write the function again to fix this small point.

3. provides a good processing method for Oracle reverse time of the date type. You can convert the date into a numeric type, and thenTo_charThe type you need is displayed.

4. When using select into, be sure to use this method only when data is identified. if the query result is null, an error is returned. another case is that multiple pieces of data will also report an error. therefore, the cursor should be made as cheap as possible. this reduces the probability of errors.

5. Note that rownum does not support sorting, that is, if you want to use this to control the number of rows, you will find that it is not displayed in the order you specified ., this is a very difficult task. and if you use a statement like rownum = 2 to output the second line, it will not work. 6. The most disgusting point is that Oracle's null judgment is abnormal to the extreme. if you say a variable AA = NULL, it cannot be determined. although AA is indeed empty. it cannot be determined even in the selection conditions. I don't know why, so I had to use the nvl () function to judge. in addition to the condition, AA is null can be used to determine.

6. Note that the parameter name cannot matchDatabaseThe field names are the same. Otherwise, Oracle will regard this parameter name as the field name, even if you use the table alias to distinguish it. so pay attention to this when starting the parameter name.