[转]oracle to_char 用法

导读:
  The following are number examples for the to_charfunction.
  to_char(1210.73, '9999.9') would return '1210.7'
  to_char(1210.73, '9,999.99') would return '1,210.73'
  to_char(1210.73, '$9,999.00') would return '$1,210.73'
  to_char(21, '000099') would return '000021'
  
  The following is a list of valid parameters when the to_charfunction is used to convert a date to a string. These parameters can be used in many 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 for the to_charfunction.
  to_char(sysdate, 'yyyy/mm/dd'); would return '2003/07/09'
  to_char(sysdate, 'Month DD, YYYY'); would return 'July 09, 2003'
  to_char(sysdate, 'FMMonth DD, YYYY'); would return 'July 9, 2003'
  to_char(sysdate, 'MON DDth, YYYY'); would return 'JUL 09TH, 2003'
  to_char(sysdate, 'FMMON DDth, YYYY'); would return 'JUL 9TH, 2003'
  to_char(sysdate, 'FMMon ddth, YYYY'); would return 'Jul 9th, 2003'
  You will notice that in some examples, the 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'); would return 'July 9, 2003'
  to_char(sysdate, 'FMMON DDth, YYYY'); would return 'JUL 9TH, 2003'
  to_char(sysdate, 'FMMon ddth, YYYY'); would return 'Jul 9th, 2003'
  The zeros have been suppressed so that the day component shows as "9" as opposed to "09".
   Oracle函数to_char转化数字型指定小数点位数的用法
  to_char,函数功能,就是将数值型或者日期型转化为字符型。
  比如最简单的应用:
  /*1.0123--->'1.0123'*/
  Select TO_CHAR(1.0123) FROM DUAL
  /*123--->'123'*/
  Select TO_CHAR(123) FROM DUAL
  接下来再看看下面:
  /*0.123 ---> '.123' */
  SELEC TO_CHAR(0.123) FROM DUAL
  上面的结果 '.123' 在大多数情况下都不是我们想要的结果,我们想要的应该是 '0.123'。
  我们来看一下to_char函数的具体用法:
   TO_CHAR( n [, fmt [, 'nlsparam']] )
  
  该函数将NUMBER类型的n按数值格式fmt转换成VARCHAR2类型的值。'nlsparams'指定由数值格式的元素返回的字符,包括:
  .小数点字符
  .组分隔符
  .本地钱币符号
  .国际钱币符号
  变元的形式为:
  'NLS_NUMERIC_CHARACTERS="dg" NLS_CURRENCY="tcxt" NLS_ISO_CURRENCY=territory'
  其中d为小数点字符,g为组分隔符。
  例 :TO_CHAR (17145,'L099G999','NLS_NUMERIC_CHARACTERS=".," NLS_CURRENCY="NUD"')=NUD017,145
  通过上面的了解,再查看fmt的一些格式,我们可以用以下表达式得到'0.123'的值:
  /*0.123 ---> ' 0.123' */
  Select TO_CHAR(0.123,'0.999') FROM DUAL
  /*100.12 ---> '######' */
  Select TO_CHAR(100.12,'0.999') FROM DUAL
  /*1.12 ---> ' 1.120' */
  Select TO_CHAR(1.12,'0.999') FROM DUAL
  ' 0.123'是出来了,可是前面又多了一个空格。
  对于 100.12 的值却是######,以及'1.12'的值变成了 '1.120'。
  我们重新确定一个新的需求:
  1、去空格
  2、小数点最多4位,最少保留2位。
  1--->'1.00';1.1--->'1.00';1.12-->'1.12';1.1234--->'1.1234';
  1.12345--->'1.1235'
  最终实现如下:
  /*
  FM :除空格
  9999999.0099:允许小数点左边最大正数为7位,小数点右边最少2位,最多4位,且在第5位进行四舍五入
  */
  Select TO_CHAR(123.0233,'FM9999999.0099') FROM DUAL
   to_char函数 特殊用法
  to_char(sysdate,'d') 每周第几天
  to_char(sysdate,'dd') 每月第几天
  to_char(sysdate,'ddd') 每年第几天
  to_char(sysdate,'ww') 每年第几周
  to_char(sysdate,'mm') 每年第几月
  to_char(sysdate,'q') 每年第几季
  to_char(sysdate,'yyyy') 年
  比如要找某个时间为每周第几天就可以
  SQL> select to_char(to_date('20070101','yyyymmdd'),'d') from dual;
  Tag标签: Oracle

本文转自
http://www.cnblogs.com/wllyy189/archive/2008/06/04/1213433.html
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值