SQL中的單記錄函數(shù)1.ASCII返回與指定的字符對應(yīng)的十進(jìn)制數(shù);SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual; ;;;;;A A;;;ZERO;;SPACE--------- --------- --------- --------- 65;;;;;97;;;;;48;;;;;322.CHR給出整數(shù),返回對應(yīng)的字符;SQL> select chr(54740) zhao,chr(65) chr65 from dual;ZH C-- -趙 A3.CONCAT連接兩個字符串;SQL> select concat('010-','88888888')'轉(zhuǎn)23'高乾競電話 from dual;高乾競電話----------------010-88888888轉(zhuǎn)234.INITCAP返回字符串并將字符串的第一個字母變?yōu)榇髮?SQL> select initcap('smith') upp from dual;UPP-----Smith5.INSTR(C1,C2,I,J)在一個字符串中搜索指定的字符,返回發(fā)現(xiàn)指定的字符的位置;C1;被搜索的字符串C2;希望搜索的字符串I;;搜索的開始位置,默認(rèn)為1J;;出現(xiàn)的位置,默認(rèn)為1SQL> select instr('Oracle traning','ra',1,2) instring from dual;INSTRING--------- 96.LENGTH返回字符串的長度;SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;NAMELENGTH(NAME) ADDR;;LENGTH(ADDR);;;;SAL LENGTH(TO_CHAR(SAL))------ ------------ ---------------- ------------ --------- --------------------高乾競;3 北京市海錠區(qū);;;;;69999.99;77.LOWER返回字符串,并將所有的字符小寫SQL> select lower('AaBbCcDd')AaBbCcDd from dual;AABBCCDD--------aabbccdd8.UPPER返回字符串,并將所有的字符大寫SQL> select upper('AaBbCcDd') upper from dual;UPPER--------AABBCCDD9.RPAD和LPAD(粘貼字符)RPAD; 在列的右邊粘貼字符LPAD; 在列的左邊粘貼字符SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;LPAD(RPAD('GAO',1-----------------*******gao*******不夠字符則用*來填滿10.LTRIM和RTRIMLTRIM; 刪除左邊出現(xiàn)的字符串RTRIM; 刪除右邊出現(xiàn)的字符串SQL> select ltrim(rtrim('gao qian jing',' '),' ') from dual;LTRIM(RTRIM('-------------gao qian jing11.SUBSTR(string,start,count)取子字符串,從start開始,取count個SQL> select substr('13088888888',3,8) from dual;SUBSTR('--------0888888812.REPLACE('string','s1','s2')string希望被替換的字符或變量 s1;;;;被替換的字符串s2;;;;要替換的字符串SQL> select replace('he love you','he','i') from dual;REPLACE('H----------i love you13.SOUNDEX返回一個與給定的字符串讀音相同的字符串SQL> create table table1(xm varchar(8));SQL> insert into table1 values('weather');SQL> insert into table1 values('wether');SQL> insert into table1 values('gao');SQL> select xm from table1 where soundex(xm)=soundex('weather');XM--------weatherwether14.TRIM('s' from 'string')LEADING剪掉前面的字符TRAILING; 剪掉后面的字符假如不指定,默認(rèn)為空格符 15.ABS返回指定值的絕對值SQL> select abs(100),abs(-100) from dual;ABS(100) ABS(-100)--------- --------- 100;;;;10016.ACOS給出反余弦的值SQL> select acos(-1) from dual;ACOS(-1)---------3.141592717.ASIN給出反正弦的值SQL> select asin(0.5) from dual;ASIN(0.5)---------.5235987818.ATAN返回一個數(shù)字的反正切值SQL> select atan(1) from dual; ATAN(1)---------.7853981619.CEIL返回大于或等于給出數(shù)字的最小整數(shù)SQL> select ceil(3.1415927) from dual;CEIL(3.1415927)--------------- 420.COS返回一個給定數(shù)字的余弦SQL> select cos(-3.1415927) from dual;COS(-3.1415927)--------------- -121.COSH返回一個數(shù)字反余弦值SQL> select cosh(20) from dual;COSH(20)---------24258259822.EXP返回一個數(shù)字e的n次方根SQL> select exp(2),exp(1) from dual; EXP(2);EXP(1)--------- ---------7.3890561 2.718281823.FLOOR對給定的數(shù)字取整數(shù)SQL> select floor(2345.67) from dual;FLOOR(2345.67)-------------- 234524.LN返回一個數(shù)字的對數(shù)值SQL> select ln(1),ln(2),ln(2.7182818) from dual; LN(1);;LN(2) LN(2.7182818)--------- --------- ------------- 0 .69314718;;.9999999925.LOG(n1,n2)返回一個以n1為底n2的對數(shù) SQL> select log(2,1),log(2,4) from dual;LOG(2,1); LOG(2,4)--------- --------- 0 226.MOD(n1,n2)返回一個n1除以n2的余數(shù)SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;MOD(10,3); MOD(3,3); MOD(2,3)--------- --------- --------- 1 0 227.POWER返回n1的n2次方根SQL> select power(2,10),power(3,3) from dual;POWER(2,10) POWER(3,3)----------- ---------- 1024 2728.ROUND和TRUNC按照指定的精度進(jìn)行舍入SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)----------- ------------ ----------- ------------ 56; -55; 55; -5529.SIGN取數(shù)字n的符號,大于0返回1,小于0返回-1,等于0返回0SQL> select sign(123),sign(-100),sign(0) from dual;SIGN(123) SIGN(-100)SIGN(0)--------- ---------- --------- 1 -1 030.SIN返回一個數(shù)字的正弦值SQL> select sin(1.57079) from dual;SIN(1.57079)------------ 131.SIGH返回雙曲正弦的值SQL> select sin(20),sinh(20) from dual; SIN(20); SINH(20)--------- ---------.91294525 24258259832.SQRT返回數(shù)字n的根SQL> select sqrt(64),sqrt(10) from dual;SQRT(64); SQRT(10)--------- --------- 8 3.162277733.TAN返回數(shù)字的正切值SQL> select tan(20),tan(10) from dual; TAN(20)TAN(10)--------- ---------2.2371609 .6483608334.TANH返回數(shù)字n的雙曲正切值SQL> select tanh(20),tan(20) from dual;TANH(20)TAN(20)--------- --------- 1 2.237160935.TRUNC按照指定的精度截取一個數(shù)SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual; TRUNC1 TRUNC(124.16666,2)--------- ------------------ 100;;124.1636.ADD_MONTHS增加或減去月份SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;TO_CHA------200002SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;TO_CHA------19991037.LAST_DAY返回日期的最后一天SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;TO_CHAR(SY TO_CHAR((S---------- ----------2004.05.09 2004.05.10SQL> select last_day(sysdate) from dual;LAST_DAY(S----------31-5月 -0438.MONTHS_BETWEEN(date2,date1)給出date2-date1的月份SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;MON_BETWEEN----------- 9SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;MON_BETW--------- -6039.NEW_TIME(date,'this','that')給出在this時區(qū)=other時區(qū)的日期和時間SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time 2; (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;BJ_TIME;;LOS_ANGLES------------------- -------------------2004.05.09 11:05:32 2004.05.09 18:05:3240.NEXT_DAY(date,'day')給出日期date和星期x之后計算下一個星期的日期SQL> select next_day('18-5月-2001','星期五') next_day from dual;NEXT_DAY----------25-5月 -0141.SYSDATE用來得到系統(tǒng)的當(dāng)前日期SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;TO_CHAR(SYSDATE,'-----------------09-05-2004 星期日trunc(date,fmt)按照給出的要求將日期截斷,假如fmt='mi'表示保留分,截斷秒SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh, 2; to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;HH; HHMM------------------- -------------------2004.05.09 11:00:00 2004.05.09 11:17:0042.CHARTOROWID將字符數(shù)據(jù)類型轉(zhuǎn)換為ROWID類型SQL> select rowid,rowidtochar(rowid),ename from scott.emp;ROWID;;;ROWIDTOCHAR(ROWID) ENAME------------------ ------------------ ----------AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITHAAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLENAAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARDAAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES43.CONVERT(c,dset,sset)將源字符串 sset從一個語言字符集轉(zhuǎn)換到另一個目的dset字符集SQL> select convert('strutz','we8hp','f7dec') 'conversion' from dual;conver------strutz44.HEXTORAW將一個十六進(jìn)制構(gòu)成的字符串轉(zhuǎn)換為二進(jìn)制45.RAWTOHEXT將一個二進(jìn)制構(gòu)成的字符串轉(zhuǎn)換為十六進(jìn)制46.ROWIDTOCHAR將ROWID數(shù)據(jù)類型轉(zhuǎn)換為字符類型47.TO_CHAR(date,'format')SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;TO_CHAR(SYSDATE,'YY-------------------2004/05/09 21:14:4148.TO_DATE(string,'format')將字符串轉(zhuǎn)化為ORACLE中的一個日期49.TO_MULTI_BYTE將字符串中的單字節(jié)字符轉(zhuǎn)化為多字節(jié)字符SQL>select to_multi_byte('高') from dual;TO--高50.TO_NUMBER將給出的字符轉(zhuǎn)換為數(shù)字SQL> select to_number('1999') year from dual; YEAR--------- 199951.BFILENAME(dir,file)指定一個外部二進(jìn)制文件SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));52.CONVERT('x','desc','source')將x字段或變量的源source轉(zhuǎn)換為descSQL> select sid,serial#,username,decode(command, 2; 0,'none', 3; 2,'insert', 4; 3, 5; 'select', 6; 6,'update', 7; 7,'delete', 8; 8,'drop', 9; 'other') cmd; from v$session where type!='background'; SIDSERIAL# USERNAME;;;;CMD--------- --------- ------------------------------ ------ 1 1;;;;;none 2 1;;;;;none 3 1;;;;;none 4 1;;;;;none 5 1;;;;;none 6 1;;;;;none 7;;;1275;;;;;none 8;;;1275;;;;;none 9;;;;;20 GAO;select 10;;;;;40 GAO;none53.DUMP(s,fmt,start,length)DUMP函數(shù)以fmt指定的內(nèi)部數(shù)字格式返回一個VARCHAR2類型的值SQL> col global_name for a30SQL> col dump_string for a50SQL> set lin 200SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;GLOBAL_NAME;DUMP_STRING------------------------------ --------------------------------------------------ORACLE.WORLDTyp=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D54.EMPTY_BLOB()和EMPTY_CLOB()這兩個函數(shù)都是用來對大數(shù)據(jù)類型字段進(jìn)行初始化操作的函數(shù)55.GREATEST返回一組表達(dá)式中的最大值,即比較字符的編碼大小.SQL> select greatest('AA','AB','AC') from dual;GR--ACSQL> select greatest('啊','安','天') from dual;GR--天56.LEAST返回一組表達(dá)式中的最小值 SQL> select least('啊','安','天') from dual;LE--啊57.UID返回標(biāo)識當(dāng)前用戶的唯一整數(shù)SQL> show userUSER 為'GAO'SQL> select username,user_id from dba_users where user_id=uid;USERNAME USER_ID------------------------------ ---------GAO2558.USER返回當(dāng)前用戶的名字SQL> select user from; dual;USER------------------------------GAO59.USEREVN返回當(dāng)前用戶環(huán)境的信息,opt可以是:ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZEISDBA; 查看當(dāng)前用戶是否是DBA假如是則返回trueSQL> select userenv('isdba') from dual;USEREN------FALSESQL> select userenv('isdba') from dual;USEREN------TRUESESSION返回會話標(biāo)志SQL> select userenv('sessionid') from dual;USERENV('SESSIONID')-------------------- 152ENTRYID返回會話人口標(biāo)志SQL> select userenv('entryid') from dual;USERENV('ENTRYID')------------------ 0INSTANCE返回當(dāng)前INSTANCE的標(biāo)志SQL> select userenv('instance') from dual;USERENV('INSTANCE')------------------- 1LANGUAGE返回當(dāng)前環(huán)境變量SQL> select userenv('language') from dual;USERENV('LANGUAGE')----------------------------------------------------SIMPLIFIED CHINESE_CHINA.ZHS16GBKLANG返回當(dāng)前環(huán)境的語言的縮寫SQL> select userenv('lang') from dual;USERENV('LANG')----------------------------------------------------ZHSTERMINAL返回用戶的終端或機(jī)器的標(biāo)志SQL> select userenv('terminal') from dual;USERENV('TERMINA----------------GAOVSIZE(X)返回X的大小(字節(jié))數(shù)SQL> select vsize(user),user from dual;VSIZE(USER) USER----------- ------------------------------ 6 SYSTEM60.AVG(DISTINCTALL)all表示對所有的值求平均值,distinct只對不同的值求平均值SQLWKS> create table table3(xm varchar(8),sal number(7,2));語句已處理。 SQLWKS>insert into table3 values('gao',1111.11);SQLWKS>insert into table3 values('gao',1111.11);SQLWKS>insert into table3 values('zhu',5555.55);SQLWKS> commit;SQL> select avg(distinct sal) from gao.table3;AVG(DISTINCTSAL)---------------- 3333.33SQL> select avg(all sal) from gao.table3;AVG(ALLSAL)----------- 2592.5961.MAX(DISTINCTALL)求最大值,ALL表示對所有的值求最大值,DISTINCT表示對不同的值求最大值,相同的只取一次SQL> select max(distinct sal) from scott.emp;MAX(DISTINCTSAL)---------------- 500062.MIN(DISTINCTALL)求最小值,ALL表示對所有的值求最小值,DISTINCT表示對不同的值求最小值,相同的只取一次SQL> select min(all sal) from gao.table3;MIN(ALLSAL)----------- 1111.1163.STDDEV(distinctall)求標(biāo)準(zhǔn)差,ALL表示對所有的值求標(biāo)準(zhǔn)差,DISTINCT表示只對不同的值求標(biāo)準(zhǔn)差SQL> select stddev(sal) from scott.emp;STDDEV(SAL)----------- 1182.5032SQL> select stddev(distinct sal) from scott.emp;STDDEV(DISTINCTSAL)------------------- 1229.95164.VARIANCE(DISTINCTALL)求協(xié)方差 SQL> select variance(sal) from scott.emp;VARIANCE(SAL)------------- 1398313.965.GROUP BY主要用來對一組數(shù)進(jìn)行統(tǒng)計SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno; DEPTNO; COUNT(*); SUM(SAL)--------- --------- --------- 10 3;;;8750 20 5;;10875 30 6;;;940066.HAVING對分組統(tǒng)計再加限制條件SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5; DEPTNO; COUNT(*); SUM(SAL)--------- --------- --------- 20 5;;10875 30 6;;;9400SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ; DEPTNO; COUNT(*); SUM(SAL)--------- --------- --------- 20;;;;;; 5;;10875 30 6;;;940067.ORDER BY用于對查詢到的結(jié)果進(jìn)行排序輸出SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc; DEPTNO ENAME;SAL--------- ---------- --------- 10 KING;5000 10 CLARK2450 10 MILLER; 1300 20 SCOTT3000 20 FORD;3000 20 JONES2975 20 ADAMS1100 20 SMITH;800 30 BLAKE2850 30 ALLEN1600 30 TURNER; 1500 30 WARD;1250 30 MARTIN; 1250 30 JAMES;950Oracle 最常用功能函數(shù)經(jīng)典匯總來源：ChinaITLab 收集整理2004-6-14 10:58:00* SQL Group Function* s (num can be a column or expression) (null values are ign* ored, default between distinct and all is all)*********************************** ********************************************AVG([distinct or all] num)　-- average value COUNT(distinct or all] num) -- number of values MAX([distinct or all] num)　-- maximum valueMAX([distinct or all] num)　-- minimum value STDDEV([distinct or all] num)　 -- standard deviation SUM([distinct or all] num)　-- sum of valuesVARIANCE([distinct or all] num) -- variance of values 　 ;;;;******************************************************* ************************* Miscellaneaous Functions :* 　*********************************** ********************************************DECODE(expr, srch1, return1 [,srch2, return2...], default] 　 -- if no search matches the expression then the default is returned, 　 -- otherwise,the first search that matches will cause 　 -- the corresponding return value to be returned DUMP(column_name [,fmt [,start_pos [, length]]]) 　-- returns an column internal oracle format, usedfor getting info about a 　-- format options : 8 = octal, 10 = decimel, 16 = hex, 17 = characters 　-- return typecodes : 1 = varchar2, 2 = number, 8 = long, 12 = date, 　--　 23 = raw,24 = long raw, 69 = rowid, 96 = char, 106 = mlslabel GREATEST(expr [,expr2 [, expr3...]] ;;;;;;　-- returns the largest value of all expressions LEAST(expr [,expr2 [, expr3...]] ;;;;　-- returns thesmallest value of all expressions NVL(expr1 ,expr2 ;;;;　-- if expr1 is not null, it is returned, otherwise expr2 is returned SQLCODE ;;;;;;　-- returns sql error code query, of last error.　Can not be used directly in 　-- value must be set to local variable first SQLERRM ;;;;;; 　-- returns sqlin query, error message of last error.　Can not be used directly 　-- value must be set to local variable first UID ;;;;;;　-- returns the user id of the user you are logged on as 　-- useful in selecting information from low level sys tables USER ;;;;　-- returns theuser name of the user you are logged on as USERENV('option') ;;　-- returns information about the user you are logged on as 　-- options : ENTRYID, SESSIONID, TERMINAL,LANGUAGE, LABEL, OSDBA 　--　 (all options not available inall Oracle versions) VSIZE(expr) ;;;;;;　-- returns the number of bytes used by the expression 　-- useful in selecting information about table space requirements 　;;;;*********************************** ********************************************* SQL Date Functions (dt represe* nts oracle date and time) * (functions return * an oracle date unless otherwise specified)******************************************************* ************************ADD_MONTHS(dt, num)　-- adds num months to dt (num can be negative) LAST_DAY(dt) -- last day of month inmonth containing dt MONTHS_BETWEEN(dt1, dt2) -- retudt2 rns fractional value of months between dt1, NEW_TIME(dt, tz1, tzzone 2 2)　 -- dt = date in time zone 1, returns date in time NEXT_DAY(dt, str)-- dateetc..) of first (str) after dt (str = ' Monday', SYSDATE　-- present system date ROUND(dt [,fmt]　-- rounds dt as specified by format fmt TRUNC(dt [,fmt]　-- truncates dt as specified by format fmt 　;;;;******************************************************* ************************* Number Functions :* 　******************************************************* ************************ABS(num) -- absolutevalue of num CEIL(num)-- smallest integer > or = num COS(num) -- cosine(num), num in radians COSH(num)　　-- hyperbolic cosine(num) EXP(num)-- e raised to the num power FLOOR(num)　 -- largest integer < or = num LN(num)　-- natural logarithm of num LOG(num2, num1)　-- logarithm base num2 of num1 MOD(num2, num1)　-- remainder of num2 / num1 POWER(num2, num1)　　-- num2 raised to the num1power ROUND(num1 [,num2]　 -- num1 rounded to num2 decimel places (default 0) SIGN(num)-- sign of num * 1, 0 if num = 0 SIN(num)-- sin(num), num in radiansSINH(num)-- hyperbolic sine(num) SQRT(num)-- square root of num ;;;;TAN(num) -- tangent(num), num in radians TANH(num)　　-- hyperbolic tangent(num)TRUNC(num1 [,num2]　 -- truncatenum1 to num2 decimel places (default 0) 　;;;;******************************************************* ************************* String Functions, * String Result :　******************************************************* ************************(num)　 -- ASCIIcharacter for num CHR(num)-- ASCII character for num CONCAT(str1, str2)　-- str1 concatenated with str2 (same as str1str2) INITCAP(str)-- capitalize first letter of each Word in str LOWER(str)　-- str with all letters in lowercase LPAD(str1, num [,str2]) -- left spaces) pad str1 to length num with str2 (default LTRIM(str [,set])　 -- remove set from leftside of str (default spaces) NLS_INITCAP(str [,nls_val]) -- same as initcap for different languages NLS_LOWER(str [,nls_val])　 -- same as lower fordifferent languages REPLACE(str1, str2 [,str3]) -- replaces str2 with str3 in str1 -- deletes str2 from str1 if str3 is omitted RPAD(str1, num [,str(default spaces) 2]) -- right pad str1 tolength num with str2 RTRIM(str [,set])　spaces) -- remove set from right side of str (default SOUNDEX(str)-- phonetic representation of str SUBSTR(str, num2 [,num1])　 -- substring of str,starting with num2, -- omitted) num1 characters (to end of str if num1 is SUBSTRB(str, num2 [,bytes num1])　-- same as substr but num1, num2 expressed in TRANSLATE(str, set1,set2)　-- replaces set1 in str with set2 -- truncated if set2 is longer than set1, it will be UPPER(str)　-- str with all letters in uppercase 　; ;;;*********************************** ********************************************* String Functions, * Numeric Result :　******************************************************* ************************　;;;;ASCII(str)　　-- ASCII value of str INSTR(str1, str2 [,num1 [,num2]]) -- position of num2th occurrence of 　 -- str2 in str1, starting at num1 　 -- (num1, num2 default to 1) INSTRB(str1, str2 [,num1 [num2]]) -- same as instr, byte values for num1, num2LENGTH(str)　 -- number of characters in str LENGTHB(str)　-- number of bytes in str NLSSORT(str [,nls_val])　 -- nls_val byte value of str 　;;;;******************************************************* ************************* SQL Conversion Functions　* 　******************************************************* ************************CHARTOROWID(str)　 -- converts str to ROWID CONVERT(str, chr_set2 [,chr_set1]) -- converts str to chr_set2 character set -- chr_set1 default is the datbase HEXTORAW(str)　　 -- converts hex string value to internal raw values RAWTOHEX(raw_val) -- converts raw hex value to hex string value ROWIDTOCHAR(rowid)　　-- converts rowid to 18 character string format TO_CHAR(expr [,fmt])fmt 　-- converts expr(date or number) to format specified by TO_DATE(str [,fmt])　 -- converts string to date TO_MULTI_BYTE(str)-- converts single byte string to multi byte string TO_NUMBER(str [,fmt]) -- converts str to a number formatted by fmt TO_SINGLE_BYTE(str)　 -- converts multi byte string to single byte string 　; ;;;******************************************************* ************************* SQL Date Formats　* 　*********************************** ********************************************　;;;;BC, B.C.BC indicator ;;;;AD, A.D.AD indicator ;;;;CC, SCC Century Code (SCC includes spaceor - sign) YYYY, SYYYY 4 digit year (SYYYY includes space or - sign) IYYY4 digit ISO year Y,YYY　 4 digit year with comma YYY, YY, or Y　 last 3, 2, or 1 digit of year YEAR, SYEAR year spelled out(SYEAR includes space or - sign) RR　last 2 digits ofyear in prior or next century Q　 quarter or year, 1 to 4 ;;;;MM　month - from 01 to 12 MONTH　 month spelled out ;;;;MON month 3 letter abbreviation ;;RM　roman numeral for month ;;;;WW　week of year, 1 to 53 IW　ISO week of year, 1 to 52 or 1 to 53 W　 week of month, 1to 5 (week 1 begins 1st day of the month) D　 day of week, 1 to 7 ; ;;;DD　day of month, 1 to 31 DDD day of year, 1 to 366 ;;;;;;DAY day of week spelled out, nine characters right padded DY　day abbreviation ;;J　 # ofdays since Jan 1, 4712 BC HH, HH12hour of day, 1 to 12 ;;;;HH24hour of day, 0 to 23 MI　minute of hour, 0 to 59 ;;;;SS　second of minute, 0 to 59 SSSSS　 seconds past midnight, 0 to 86399 AM, A.M.am indicator ;;;;PM, P.M.pm indicator ;;;;any pUCtuation　punctuation between format items, as in 'DD/MM/YY' any texttext between format items ;;TH　converts 1 to '1st', 2 to '2nd',and so on SP　converts 1 to 'one', 2 to 'two', and so on SPTHconverts 1 to 'FIRST', 2 to 'SECOND', and so on FX　fillexact : uses exact pattern matching FM　fill mode　: toggles suppression of blanks in output