Single recording function 1.ascii in SQL returns a decimal number corresponding to the specified character; SQL> SELECT ASCII ('A') A, ASCII ('A') A, ASCII ('0') ZERO, ASCII ('' ) Space from dual; a a a zero space --------------------------------- 65 97 48 32 2 .Chr gives an integer, returns the corresponding character; SQL> SELECT CHR (54740) ZHAO, CHR (65) chr65 from dual; zh C - - Zhao A 3.concat Connect two strings; SQL> SELECT Concat (' 010 - ',' 88888888 ') ||' Turn 23 'Gao Dynasty sports phone from dual; high-job call ---------------- 010-88888888 turn 23 4.initcap Return String and turn the first letter of the string to uppercase; SQL> Select Initcap ('Smith') UPP from Dual; UPP ----- Smith 5.instr (C1, C2, I, J) in a character String searches for specified characters, returns the position of the specified character; C1 Searched String C2 WY WY Search The Start Location of Search I Search, the default is 1 J appears, default is 1SQL> SELECT INSTR ('Oracle TRANING ',' ra ', 1, 2) Instring from Dual; Instring --------- 9 6.length Returns the length of the string; SQL> SELECT NAME, Length (Name), Addr, Length (AddR) , Sal, Length (to_CHAR (SAL)) from GAO.NCHAR_TST; Name Length (Name) AddR Length (Addr) Sal length (to_char (sal)) --------------- - - ---------------------------------------------------------------- -------- High-dried competition 3 Beijing Sea Ingoto 6 9999.99 7 7.Lower Returns a string and puts all characters lowercase SQL> Select Lower ('aabbcdd') aabbccdd from dual; aabbccdd -------- Aabccdd 8.upper Return characters String, and all character uppercase SQL> select Upper ('aabbccdd') Upper from Dual; Upper -------- Aabbccdd 9.rpad and LPAD (Paste Character) RPAD Paste Character LPAD in the right side of the column Left Paste Character SQL> SELECT LPAD (RPAD ('GAO', 10, '*'), 17, '*') from Dual; LPAD (RPAD ('Gao', 1 ---------- ------ ******* gao ******* Not enough characters use * to fill 10.LTRIM and RTRIMLTRIM Delete the string RTRIM in the left RTRIM to delete the string SQL> SELECT LTRIM (RTRIM ('GAO Qian Jing', ''), ') from Dual; LTRIM (RTRIM ('
------------- Gao Qian Jing 11.Substr (String, Start, Count) Subsidence, starting from Start, take count SQL> SELECT SUBSTR ('13088888888 ", 3, 8 From Dual; Substr ('-------- 08888888 12.Replace (' string ',' s1 ',' s2 ') String Write the string S2 to be replaced by the string S2 to replace String SQL> SELECT Replace ('He Love You', 'He', 'I') from Dual; Replace ('H ---------- i Love You 13. Soundex Returns a given String SQL> CREATE 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 -------- WeatherWether 14.Trim ('s' from 'string') Leading Cut The front character trailing cut off the following characters If not specified, the default is the absolute value of the specified value to the specified value SQL> SELECT ABS (100), ABS (-100) from Dual; ABS (100) ABS (-100 ) --------- --------- 100 100 16.AS gives anti-surpass strolling SQL> SELECT ACOS (-1) from dual; ACOS (-1) ---- ----- 3.1415927 17.aSin Ges SQL> SELECT Asin (0.5) from Dual; Asin (0.5) ---------. 52359878 18.atan Returns a number of anterior rigid value SQL> SELECT Atan (1) from Dual; Atan (1) --- -----. 78539816 19.ceil Return to the minimum integer of the number to give the number SQL> SELECT CEIL (3.1415927) from Dual; CEIL (3.1415927) --------------- 4 20.COS Returns a given number cosine SQL> SELECT COS (-3.1415927) from dual; cos (-3.1415927) --------------- -1 21.cosh Returns a number Anti-hosh (20) from Dual; Cosh (20) ---------- 242582598 22.EXP Returns N Mutang SQL> SELECT Exp (2), EXP (1 ) from dual; exp (2) exp (1) ----------------- 7.3890561 2.7182818 23.floor to a given digital tap SQL> SELECT FLOOR (2345.67) from Dual; Floor (2345.67) -------------- 2345 24.Ln Returns a number of logs SQL> SELECT LN (1), LN (2), LN (2.7182818) from dual;
LN (1) ln (2) ln (2.7182818) ----------------------------- 0 .69314718 .9999999. LOG (N1, N2) Returns a log (2, 4) from Dual; log (2, 1) log (2, 4) - LOG (2, 1) log (2, 4) - ------------- 0 2 26.MOD (N1, N2) Returns a N1 divided by N2 SQL> SELECT MOD (10, 3), MOD (3, 3), Mod (2,3) from Dual; Mod (10, 3) MOD (3, 3) MOD (2, 3) --------------------- ---- 1 0 2 27.Power Return N1 N2 Square SQL> SELECT POWER (2, 10), Power (3, 3) from Dual; Power (2, 10) Power (3, 3) - ------------------ 1024 27 28.Round and Trunc Follow SQL> SELECT ROUND (55.5), Round (-55.4), Trunc (55.5) ), trunc (-55.5) from Dual; Round (55.5) Round (-55.5) Trunc (-55.5) --------------------- - ----------- ------------ 56 -55 55 -55 29. Sign Take the symbol of the digital N, more than 0 returns 1, less than 0 returns -1, Is equal to 0 Return 0SQL> SELECT SIGN (123), SIGN (-100), SIGN (0) from Dual; SIGN (123) SIGN (-100) SIGN (0) -------------- ------------- 1 -1 0 30.Sin Returns a Digital Strine SQL> SELECT SIN (1.57079) from Dual; SIN 1.57079) ------------ 1 31.Sigh Returns a double-sinus sine value SQL> SINE (20), SINH (20) from dual; sin (20) sinh (20) --- ------ ---------. 91294525 242582598 32.SQRT Return to the root SQL> SELECT SQRT (64), SQRT (10) from Dual; SQRT (64) SQRT (10) - --------------- 8 3.1622777 33.Tan Return to the number of positive cuts SQL> SELECT TAN (20), TAN (10) from dual; tan (20) Tan (10) ----------------- 2.2371609.64836083 34.Tanh Return to Digital N Double Crystal Differential SQL> SELECT TANH (20), TAN (20) from Dual; Tanh (20 ) Tan (20) ----------------- 1 2.2371609 35.Trunc takes a number of SQL> SELECT TRUNC (124.1666, -2) Trunc1, Trunc (124.16666) , 2) from Dual; Trunc1 Trunc (124.16666, 2) -------------------------- 100 124.16 36.Add_months increase or minus the month SQL>
Select to_char (add_months ('199912', 'YYYYMM'), 2), 'YYYMM') from Dual; TO_CHA ------ 200002SQL> SELECT TO_CHAR (add_months (to_date (to_date ('199912', 'YYYYMM') , -2), 'YYYYMM') from Dual; TO_CHA ------ 199910 37.Last_day return date SQL> select to_char (sysdate, 'yyyyy.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; la cast_day (s ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1999 ',' 19- March -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 --------- -60 39.new_time (Date, 'this', 'That') gives this in THIS Time Zone = Date and Time 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:32 40.next_day (date, 'day') calculated the date Date and the week X Weekly Date SQL> SELECT NEXT_DAY ('18 - May -2001 ',' Friday ') Next_Day from Dual; Next_DAY ---------- 25 - May -01 41.sdate is used to get system Current date SQL> SELECT TO_CHAR (Sysdate, 'DD-mm-yyyy day') from dual; to_char (sysdate, '----------------- 09-05-2004 Sunday Trunc (DATE, FMT) The date will be truncated, if FMT = 'mi' is represented, cut off second sql> select to_char (trunc (tysdate, '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:00 42.Chartorowid converts the character data type to ROWID type SQL> SELECT ROWID, ROWIDTOCHAR (ROWID), ENAME from Scott.emp; RowId Rowidtochar (Rowid) ENAME ------------------------------- -AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITHAAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLENAAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARDAAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES 43.CONVERT (c, dset, sset) the source string sset from one character set to another language character set object dset SQL> select convert ( 'strutz', 'we8hp' , 'f7dec') "conversion" from dual; control ------ Strutz 44.Hextoraw converts a hexadecimal string to binary 45.RawTohext convert a binary string string to hex 46. RowidToChar converts ROWID data type to character type 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:41 48.to_date (string, 'format') Transforms a string to an Date in Oracle 49.to_multi_byte transforms the single-byte character in the string into multi-byte characters SQL> SELECT TO_MULTI_BYTE 'High') from dual; TO - High 50.to_number will be converted to the characters to digital SQL> SELECT TO_NUMBER ('1999') Year from Dual; Year --------- 1999 51.bfilename DIR, FILE) Specify an external binary file SQL> INSERT INTO file_tb1 value (bFilename ('LOB_DIR1', 'Image1.gif')); 52.convert ('x', 'dec', 'Source') will x field or Source Source Source Source Convert to 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 WhereE Type! = 'Background';
SID Serial # 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 None 53.dump (S, FMT, Start , length) DUMP function to specify the internal number format fmt returns a value of type 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 --------------------------------- -------------------------------- ORAC Le.World Typ = 1 LEN = 12 CHARACTERSET = zHS16GBK: W, O, R, L, D 54.empty_blob () and EMPTY_CLOB () These two functions are functions used to initialize the big data type field 55 .Greatest returns the maximum value in a set of expressions, that is, compare characters encoded size. SQL> SELECT GREATEST ('AA', 'AB', 'AC') from Dual; GR - ACSQL> SELECT GREATEST ('ah " , 'An', 'Day') from Dual; GR - Day 56.Least Returns the minimum of the minimum SQL> Select Least (',', 'An', 'Day') from Dual; LE- - Ah 57.uid Returns the only integer integer SQL> show useruser for the current user "GAO" SQL> SELECT UserName, user_id from dba_users where user_id = uid; username user_id --------------- ----------------------- Gao 25 58.user Returns the current user's name SQL> SELECT User from Dual;
User ------------------------------ Gao 59.Userevn Returns the information of the current user environment, OPT can be: entryid, sessionID, Terminal, ISDBA, LABLE, LANGUAGE, Client_info, Lang, VSIZEISDBA View the current user is DBA if it returns Truesql> Select Useerenv ('isdba') from dual; useern ------ FalseSQL> Select Userenv ('isdba' ) from dual; useern ------ TRUESSSION Returns Session Sign SQL> SELECT Userenv ('sessionID') from Dual; Useerenv ('sessionID') ----------------- --- 152ENTRYID Return to Session Population Sign SQL> SELECT Userenv ('Entryid') from Dual; Useerenv ('Entryid') ------------------ 0Instance Returns the current instance sign SQL> SELECT Userenv ('Instance') from Dual; Userenv ('Instance') ------------------ 1Language Returns the current environment variable SQL> SELECT Userenv ('Language' ) from Dual; Userenv ('language') ---------------------------------------------------------------------------------------------------- ------------ Simplified Chinese_China.zhs16gbklang Returns the language of the current environment SQL> SELECT Userenv ('lang') from Dual; Useerenv ('lang') --------- ----------------------------------------- ENTERMINAL Returns the terminal or machine Sign SQL> SELECT Userenv ('Terminal') from Dual; Useerenv ('Termina - -------------- GAOVSIZE (X) Returns the size of X (bytes) SQL> SELECT VSIZE (User), user from dual; vsize (user) user ------ -------------------------------- 6 System 60.avg (DistINCT | All) All Represents to all Value average, DistINCT is only for different values for different values SQLWKS> CREATE TABLE3 (XM VARCHAR (8), SAL Number (7, 2)); statement has been processed.
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) ---------------------------- 3333.33 SQL> SELECT AVG (All sal) from gao.table3; avg (allsal) --- ------ 2592.59 61.max (DistINCT | All) Ask the maximum, all represents the maximum value for all values, Distinct indicates the maximum value for different values, the same only one SQL> SELECT MAX (distinct sal) from scott.emp; max (distinctsal) ----------------000 62.MIN (DistINCT | ALL) Summary, ALL indicates that the minimum value is Value, Distinct indicates the minimum value of different values, the same only one SQL> SELECT Min (all sal) from gao.table3; min (allsal) ----------- 1111.11 63.stddev ( Distinct | All) Seeking standards, all represents all values of all values, Distinct represents only different values of different values, SQL> SELECT STDDEV (SAL) from Scott.emp; Stddev (SAL) -------- ----- 1182.5032 SQL> SELECT STDDEV (Distinct Sal) from Scott.emp; StdDev (Distinctsal) ------------------- 1229.951 64.Variance (Distinct | All ) Ask for partial difference SQL> SELECT VARIANCE (SAL) from Scott.emp; Variance (SAL) ------------- 1398313. 9 65.Group BY mainly used to count SQL> SELECT Deptno, count (*), sum (sal) from scott.emp group by deptno; deptno count (*) SUM (SAL) ----- ---- ---------- --------- 10 3 8750 20 5 10875 30 6 9400 66.Having to group statistics to add restrictions 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 9400 67.Order By for query The result is sorted by SQL> Select Deptno, Ename, Sal from Scott.emp Order by DePtno, Sal Desc; DePtno ENAME SAL ------------------ --- ------ 10 King 5000 10 Clark 2450 10 Miller 1300 20 SCOT 3000 20 ADAMS 1100 20 SMITH 800 30 Blake 2850 30 Allen 1600 30 Turner 1500 30 Ward 1250 30 Martin 1250 30 James 950Oracle is most commonly used Functional function classic summary Source: chinaitlab Collection 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 Value
Max ([Distinct or All] Num) - Minimum Value
STDDEV ([Distinct or All] Num) - Standard Deviation
SUM ([Distinct or All] Num) - Sum of Values
Variance ([Distinct or All] Num) - Variance of Values
*********************************************************** *****************************
* MiscellaneAous functions: *
************************ ******** ************************* ****************************
Decode (expr, srch1, return1 [, srch2, return2 ...], default]
- if no search matches the expression of the default is returned,
- OtherWise, The First Search That Matches Will Cause
- The Corresponding Return Value to Be Returneddump (Column_name [, FMT [, Start_POS [, Length]]]])
- Returns An Column Info About A of Returns An Column 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 themallest 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 buy 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) - datetc ..) 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) - Smallst 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 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 RADIANS SINH (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 controlnated with str2 (Same as str1 || str2) 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 (str3, 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, STARTING WITH NUM2, - omitted) Num1 Characters (to end of striff " Substrb (Str, Num2 [, Bytes Num1]) - Same as Substr But Num1, Num2 Expressed in Translate (STR, SET1, SET2) - Replace Set1 in Str with set2 - Truncated if set2 is for than set1, IT Will B 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, start at num1 - (Num1, Num2 Default to 1) INSTRB (STR1, STR2 [, Num1 [Num2]]) - Same as INSTR, BYTE VALUES for NUM1, NUM2 Length (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) Iyyy 4 Digit Iso Year Y, YYY 4 Digit Year with Comma YYY, YY, OR Y Last 3, 2, or 1 Digit of yearyear, 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 days 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, HH12 HOUR of Day, 1 to 12 HH24 HOUR 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 Text TEXT BETWEEN FORMAT ITEMS TH Converts 1 to '1st', 2 to '2nd', Ando ON SP Converts 1 To 'One', 2 to 'Two', Ando ON SPTH Converts 1 To 'First', 2 to 'Second', Ando ON FX Fillexact: Uses Exact Pattern mathing FM Fill Mode: Toggles Suppression Of Blanks in Output
