SQL functions numeric and date Speaker notes contain

SQL functions - numeric and date Speaker notes contain additional information!

Numeric functions PAY_ ---1111 2222 3333 4444 5555 6666 NAME ----------Linda Costa John Davidson Susan Ash Stephen York Richard Jones Joanne Brown JO -CI IN AP CM CI IN STARTDATE SALARY BONUS ---------15 -JAN-97 45000 1000 25 -SEP-92 40000 1500 05 -FEB-00 25000 500 03 -JUL-97 42000 30 -OCT-92 50000 2000 18 -AUG-94 48000 2000 SQL> SELECT ROUND(salary/12, 2) 2 FROM first_pay; ROUND(SALARY/12, 2) ---------3750 3333. 33 2083. 33 3500 4166. 67 4000 number of decimal places This command is rounding the calculation of salary divided by 12. It is rounding to 2 decimal places.

Numeric functions SQL> SELECT salary/7 2 FROM first_pay; SALARY/7 ----6428. 5714. 2857 3571. 4286 6000 7142. 8571 6857. 1429 Both rounding and truncating are Rounding goes to. 29, truncating Rounding goes to. 43, truncating No rounding or truncating Rounding goes to. 86, truncating Both rounding and truncating are . 57 leaves at. 28 leaves at. 42 leaves at. 85. 14 SQL> SELECT ROUND(salary/7, 2), TRUNC(salary/7, 2) 2 FROM first_pay; ROUND(SALARY/7, 2) TRUNC(SALARY/7, 2) ---------6428. 57 5714. 29 5714. 28 3571. 43 3571. 42 6000 7142. 86 7142. 85 6857. 14

Numeric functions SQL> SELECT salary, MOD(salary, 3) 2 FROM first_pay; SALARY MOD(SALARY, 3) ------------45000 0 40000 1 25000 1 42000 0 50000 2 48000 0 SQL> SELECT salary, MOD(salary, 7) 2 FROM first_pay; SALARY MOD(SALARY, 7) ------------45000 4 40000 2 25000 3 42000 0 50000 6 48000 1 SQL> SELECT salary, MOD(salary, 12) 2 FROM first_pay; SALARY MOD(SALARY, 12) -------------45000 0 40000 4 25000 4 42000 0 50000 8 48000 0 number dividing by MOD returns the remainder that results from the divide. There are three different examples. Note that the format is MOD(column, number you are dividing by)

Numeric functions SQL> SELECT salary/7, CEIL(salary/7), FLOOR(salary/7) 2 FROM first_pay; SALARY/7 CEIL(SALARY/7) FLOOR(SALARY/7) --------------6428. 5714 6429 6428 5714. 2857 5715 5714 3571. 4286 3572 3571 6000 7142. 8571 7143 7142 6857. 1429 6858 6857 SQL> SELECT salary/7, CEIL(salary/7) 2 FROM first_pay 3 WHERE CEIL(salary/7) = ROUND(salary/7, 0); SALARY/7 CEIL(SALARY/7) -------------6428. 5714 6429 6000 7142. 8571 7143 This shows CEIL and FLOOR in calculations and CEIL in a WHERE clause comparison.

Numeric functions Format: POWER(column or formula, power) To the second power To the third power To the forth power SQL> SELECT bonus, POWER(bonus, 2), POWER(bonus/100, 3), POWER(bonus/100, 4) 2 FROM first_pay; BONUS POWER(BONUS, 2) POWER(BONUS/100, 3) POWER(BONUS/100, 4) -----------------1000 10000 1500 2250000 3375 50625 500 250000 125 625 2000 4000000 8000 160000

Numeric functions SIGN(MOD(bonus, bonus) means bonus is divided by bonus and the sign of the remainder is checked to see if it is 0, >0, <0 SQL> SELECT bonus, SIGN(MOD(bonus, bonus)), SIGN(bonus/-1), SIGN(bonus) 2 FROM first_pay; BONUS SIGN(MOD(BONUS, BONUS)) SIGN(BONUS/-1) SIGN(BONUS) ----------------------1000 0 -1 1 1500 0 -1 1 2000 0 -1 1 SIGN returns a 0 if the number being evaluated is a 0, a 1 if the number being evaluated is >0, and a -1 if the number being evaluated is <0.

Date function Sysdate gets the date from the system. Notice the default presentation. SQL> SELECT sysdate 2 FROM dual; SYSDATE ----02 -JUN-00 SQL> SELECT sysdate 2 FROM sys. dual; SYSDATE ----02 -JUN-00

Date functions SQL> SELECT sysdate, ROUND(sysdate), TRUNC(sysdate) 2 FROM dual; SYSDATE ROUND(SYS TRUNC(SYS ---------03 -JUN-00 I did the first date at 7: 10 in the AM. NOTE: All three dates are the same. I then went into the system clock and changed it to PM. When I ran the command again, ROUND went to the next day. SQL> SELECT sysdate, ROUND(sysdate), TRUNC(sysdate) 2 FROM dual; SYSDATE ROUND(SYS TRUNC(SYS ---------03 -JUN-00 04 -JUN-00 03 -JUN-00

Date functions SQL> SELECT startdate, ROUND(startdate), TRUNC(startdate) 2 FROM first_pay; STARTDATE ----15 -JAN-97 25 -SEP-92 05 -FEB-00 03 -JUL-97 30 -OCT-92 18 -AUG-94 ROUND(STA ----15 -JAN-97 25 -SEP-92 05 -FEB-00 03 -JUL-97 30 -OCT-92 18 -AUG-94 TRUNC(STA ----15 -JAN-97 25 -SEP-92 05 -FEB-00 03 -JUL-97 30 -OCT-92 18 -AUG-94 The default on ROUND and TRUNC is day. MONTH and YEAR can be specified. SQL> SELECT ROUND(startdate, 'MONTH'), TRUNC(startdate, 'MONTH') 2 FROM first_pay; ROUND(STA ----01 -JAN-97 01 -OCT-92 01 -FEB-00 01 -JUL-97 01 -NOV-92 01 -SEP-94 TRUNC(STA ----01 -JAN-97 01 -SEP-92 01 -FEB-00 01 -JUL-97 01 -OCT-92 01 -AUG-94 When ROUND is used with MONTH, dates with days such as 25 -SEP are rounded to OCT and 30 -OCT are rounded to NOV. Dates with 15 -JAN and 05 -FEB are simply rounded to the first day of the month. NOTE: The day is 01 when rounded or truncated.

Date functions SQL> SELECT startdate, ROUND(startdate, 'YEAR'), TRUNC(startdate, 'YEAR') 2 FROM first_pay; STARTDATE ----15 -JAN-97 25 -SEP-92 05 -FEB-00 03 -JUL-97 30 -OCT-92 18 -AUG-94 ROUND(STA ----01 -JAN-97 01 -JAN-93 01 -JAN-00 01 -JAN-98 01 -JAN-93 01 -JAN-95 TRUNC(STA ----01 -JAN-97 01 -JAN-92 01 -JAN-00 01 -JAN-97 01 -JAN-92 01 -JAN-94 When the ROUND and TRUNC are done to a year 01 -JAN are used for day and month. For months like JUL, AUG, SEP and OCT ROUND shows the next year. For dates like JAN and FEB ROUND shows the same year.

Date functions SQL> SELECT name, startdate, MONTHS_BETWEEN(startdate, sysdate) 2 FROM first_pay; NAME ----------Linda Costa John Davidson Susan Ash Stephen York Richard Jones Joanne Brown STARTDATE MONTHS_BETWEEN(STARTDATE, SYSDATE) ---------------------15 -JAN-97 1159. 3767 25 -SEP-92 1107. 6992 05 -FEB-00 -3. 945912 03 -JUL-97 1165 30 -OCT-92 1108. 8605 18 -AUG-94 1130. 4734 THE SYSDATE being used is 03 -JUN-00. Note that 05 -FEB-00 is -3. 9… months back. Looking at 15 -JAN-97 you can see that the number is going forward. The assumption is being made that the first two digits are 20, and we are looking forward and taking the difference between 15 -JAN-2000 and 03 -JUN-2000. This is because this table was created in 2000 and therefore the date was stored with the 20.

Date functions SQL> SELECT datefst, MONTHS_BETWEEN(sysdate, datefst) FROM donor; DATEFST MONTHS_BETWEEN(SYSDATE, DATEFST) --------------------03 -JUL-98 23 24 -MAY-97 36. 353125 03 -JAN-98 29 04 -MAR-92 98. 998286 04 -APR-98 25. 998286 Since this table was created in 1999, the data is calculated with dates in 19__. NOTE: the whole year is actually stored with the date. There are 23 months between 03 -JUL-98 and 03 -JUN-00 (the sysdate).

Date functions SQL> SELECT startdate, TO_CHAR(startdate, 'MM/DD/YYYY') FROM first_pay; STARTDATE ----15 -JAN-97 25 -SEP-92 05 -FEB-00 03 -JUL-97 30 -OCT-92 18 -AUG-94 TO_CHAR(STARTDATE, 'MM/DD/YYYY') ------------------------------01/15/2097 TO_CHAR will be covered later in this presentation. 09/25/2092 It converts dates or numbers to character fields using 02/05/2000 a specified format. In this case, the format is 07/03/2097 MM/DD/YYYY to show the four character year. 10/30/2092 08/18/2094 When I show the date in a format that shows the four digit year, I can see that the first_pay database which was created in 2000 has all the years in 2000. The donor database which was created in 1999 has all the years in the 1990 s. SQL> SELECT datefst, TO_CHAR(datefst, 'MM/DD/YYYY') FROM donor; DATEFST ----03 -JUL-98 24 -MAY-97 03 -JAN-98 04 -MAR-92 04 -APR-98 TO_CHAR(DATEFST, 'MM/DD/YYYY') ---------------------------07/03/1998 05/24/1997 01/03/1998 03/04/1992 04/04/1998

Date function SQL> SELECT name, startdate, ADD_MONTHS(startdate, 6), ADD_MONTHS(startdate, -6) 2 FROM first_pay; NAME ----------Linda Costa John Davidson Susan Ash Stephen York Richard Jones Joanne Brown STARTDATE ----15 -JAN-97 25 -SEP-92 05 -FEB-00 03 -JUL-97 30 -OCT-92 18 -AUG-94 ADD_MONTH ----15 -JUL-97 25 -MAR-93 05 -AUG-00 03 -JAN-98 30 -APR-93 18 -FEB-95 ADD_MONTH ----15 -JUL-96 25 -MAR-92 05 -AUG-99 03 -JAN-97 30 -APR-92 18 -FEB-94 In one example went ahead and then back 6 months, in the other example I used 2 months. Notice that the difference between 2000 and 1999 are handled correctly. SQL> SELECT name, startdate, ADD_MONTHS(startdate, 2), ADD_MONTHS(startdate, -2) 2 FROM first_pay; NAME ----------Linda Costa John Davidson Susan Ash Stephen York Richard Jones Joanne Brown STARTDATE ----15 -JAN-97 25 -SEP-92 05 -FEB-00 03 -JUL-97 30 -OCT-92 18 -AUG-94 ADD_MONTH ----15 -MAR-97 25 -NOV-92 05 -APR-00 03 -SEP-97 30 -DEC-92 18 -OCT-94 ADD_MONTH ----15 -NOV-96 25 -JUL-92 05 -DEC-99 03 -MAY-97 30 -AUG-92 18 -JUN-94

Date function SQL> SELECT startdate, NEXT_DAY(startdate, 'MONDAY') 2 FROM first_pay; STARTDATE ----15 -JAN-97 25 -SEP-92 05 -FEB-00 03 -JUL-97 30 -OCT-92 18 -AUG-94 NEXT_DAY( ----21 -JAN-97 29 -SEP-92 07 -FEB-00 08 -JUL-97 03 -NOV-92 23 -AUG-94 Looking at the dates in first_pay, I am determining the next time a MONDAY will occur starting from that date. For example, 05 -FEB-00 is a SATURDAY, so the next time a MONDAY will occur is 07 FEB-00. SQL> SELECT sysdate, NEXT_DAY(sysdate, 'TUESDAY') 2 FROM dual; SYSDATE NEXT_DAY( -----03 -JUN-00 06 -JUN-00 Today is a SATURDAY, so in fact the next time a TUESDAY occurs will be 3 days from now on 06 -JUN-00.

Conversion functions SQL> SELECT name, startdate, TO_CHAR(startdate, 'MM/YYYY') 2 FROM first_pay; NAME STARTDATE ----------TO_CHAR(STARTDATE, 'MM/YYYY') -------------------------------------Linda Costa 15 -JAN-97 The TO_CHAR startdate has a lot of 01/2097 padding and so it wraps - even on the Oracle screen. The default column size is 80 John Davidson 25 -SEP-92 characters. This can be resized with the 09/2092 column command to be covered later. Susan Ash 02/2000 05 -FEB-00 Stephen York 07/2097 03 -JUL-97 Richard Jones 10/2092 30 -OCT-92 Joanne Brown 08/2094 18 -AUG-94 Note: The month is displayed in number format because of the MM which returns 112 and the year is displayed as a 4 digit year because of the YYYY format. See notes for additional information.

Conversion functions SQL> SELECT name, startdate, TO_CHAR(startdate, 'fm. MM/YYYY') 2 FROM first_pay; NAME STARTDATE ----------TO_CHAR(STARTDATE, 'FMMM/YYYY') ------------------------------Linda Costa 15 -JAN-97 1/2097 The fm element can be used to leading John Davidson 25 -SEP-92 zeros or padded blanks. In this case it will 9/2092 remove the leading 0 from the month. Susan Ash 2/2000 05 -FEB-00 Stephen York 7/2097 03 -JUL-97 Richard Jones 10/2092 30 -OCT-92 Joanne Brown 8/2094 18 -AUG-94

Conversion functions SQL> SELECT sysdate, TO_CHAR(sysdate, 'Q') 2 FROM dual; Q returns the quarter. June is in the second quarter. SYSDATE TO_CHAR(SYSDATE, 'Q') ---------------------------------04 -JUN-00 2 SQL> SELECT sysdate, TO_CHAR(sysdate, 'fm. DDMONTHYYYY') 2 FROM dual; NOTE the difference between the format SYSDATE TO_CHAR(SYSDATE, 'FMDDMONTHYYYY') without spaces between --------------------elements and with spaces 04 -JUN-00 4 JUNE 2000 between elements. SQL> SELECT sysdate, TO_CHAR(sysdate, 'fm. DD MONTH YYYY') 2 FROM dual; SYSDATE TO_CHAR(SYSDATE, 'FMDDMONTHYYYY') --------------------04 -JUN-00 4 JUNE 2000 fm. DD returns the day with leading zeros removed MONTH returns the month in words YYYY returns the year in 4 digits

Conversion functions SQL> SELECT TO_CHAR(sysdate, 'fm. DD "of" MONTH') 2 FROM dual; Double quotes are used to enclose character strings that are embedded in the format. TO_CHAR(SYSDATE, 'FMDD"OF"MONTH') -----------------------4 of JUNE DDSP means spelled out DD. SQL> SELECT TO_CHAR(sysdate, '"DAY" DDSP "OF" MONTH') 2 FROM dual; TO_CHAR(SYSDATE, '"DAY"DDSP"OF"MONTH') ---------------------------DAY FOUR OF JUNE SQL> SELECT TO_CHAR(startdate, 'fm. DDTH "of" MONTH') 2 FROM first_pay; TO_CHAR(STARTDATE, 'FMDDTH"OF"MONTH') ---------------------------15 TH of JANUARY 25 TH of SEPTEMBER 5 TH of FEBRUARY 3 RD of JULY 30 TH of OCTOBER 18 TH of AUGUST DDTH adds TH or other appropriate clause to date.

Conversion functions SQL> SELECT TO_CHAR(sysdate, 'HH 24: MI: SS: AM') 2 FROM dual; TO_CHAR(SYSDATE, 'HH 24: MI: SS: AM') ----------------------14: 23: 48: PM SQL> SELECT TO_CHAR(sysdate, 'HH 12: MI: SS: AM') 2 FROM dual; TO_CHAR(SYSDATE, 'HH 12: MI: SS: AM') -----------------------02: 24: 56: PM SQL> SELECT TO_CHAR(sysdate, 'HH: MI: SS') 2 FROM dual; TO_CHAR(SYSDATE, 'HH: MI: SS') --------------------02: 31: 17 Note that AM changes to PM when appropriate. PM could also have been used. HH 24 is 24 hour clock and HH 12 is 12 hour clock. HH hour of day.

Conversion functions SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR'; Session altered. SQL> INSERT INTO first_pay 2 VALUES('8888', 'Paula Adams', 'IN', '12 -DEC-98', 45000, 2000); 1 row created. SQL> SELECT name, TO_CHAR(startdate, 'DD-MON-YYYY') 2 FROM first_pay; NAME ----------Linda Costa John Davidson Susan Ash Stephen York Richard Jones Joanne Brown Donald Brown Paula Adams 8 rows selected. TO_CHAR(STARTDATE, 'DD-MON-YYYY') ---------------------15 -JAN-2097 25 -SEP-2092 The session is altered as 05 -FEB-2000 explained in the speaker notes. 03 -JUL-2097 Therefore, Paula Adams is given 30 -OCT-2092 a year of 1998 instead of 2098. 18 -AUG-2094 05 -NOV-2099 12 -DEC-1998

Conversion functions SQL> UPDATE first_pay 2 SET startdate = '05 -NOV-99' 3 WHERE name = 'Donald Brown'; 1 row updated. I am still in the session where RR is established so when I change the date for Donald Brown, it becomes 1999 instead of the previous 2099. SQL> SELECT name, TO_CHAR(startdate, 'DD-MON-YYYY') 2 FROM first_pay; NAME ----------Linda Costa John Davidson Susan Ash Stephen York Richard Jones Joanne Brown Donald Brown Paula Adams TO_CHAR(STARTDATE, 'DD-MON-YYYY') -----------------15 -JAN-2097 25 -SEP-2092 05 -FEB-2000 03 -JUL-2097 30 -OCT-2092 18 -AUG-2094 05 -NOV-1999 12 -DEC-1998

Conversion functions SQL> SELECT TO_CHAR(sysdate, 'fm. DDSPTH "day of" MONTH", "YYYY "at" HH 12 AM') 2 FROM dual; TO_CHAR(SYSDATE, 'FMDDSPTH"DAYOF"MONTH", "YYYY"AT"HH 12 AM') -------------------------------------FOURTH day of JUNE, 2000 at 4 PM Note: MONTH”, “YYYY means that there is no space after month and only the space embedded in the literal before year. Notice also that HH gives the hour only. The actual time is 4: 07 but I did not ask for minutes.

Conversion functions SQL> DESC first_pay; Name Null? ----------------PAY_ID NAME JOBCODE STARTDATE SALARY BONUS Type ---VARCHAR 2(4) VARCHAR 2(20) CHAR(2) DATE NUMBER(9, 2) NUMBER(5) Note that BONUS has no decimal places, put in formatting I want it to display with decimal places. This can be done through formatting. SQL> SELECT pay_id, name, TO_CHAR(salary, '$99, 999. 99'), TO_CHAR(bonus, '$99, 999. 99') 2 FROM first_pay; PAY_ ---1111 2222 3333 4444 5555 6666 7777 8888 NAME TO_CHAR(SAL TO_CHAR(BON ---------- -----Linda Costa $45, 000. 00 $1, 000. 00 John Davidson $40, 000. 00 $1, 500. 00 Susan Ash $25, 000. 00 $500. 00 Stephen York $42, 000. 00 $2, 000. 00 Richard Jones $50, 000. 00 $2, 000. 00 Joanne Brown $48, 000. 00 $2, 000. 00 Donald Brown $45, 000. 00 $2, 000. 00 Paula Adams $45, 000. 00 $2, 000. 00 This is a floating $ sign that floats up to the first significant digit. Note that with 500 the comma gets suppressed because there is no significant digit in the thousands position.

Conversion functions SQL> SELECT name, TO_CHAR(salary, '99, 999. 99'), TO_CHAR(bonus, '9, 999') 2 FROM first_pay; NAME TO_CHAR(SA TO_CHA ---------- -----Linda Costa 45, 000. 00 1, 000 John Davidson 40, 000. 00 1, 500 Susan Ash 25, 000. 00 500 Stephen York 42, 000. 00 2, 000 Richard Jones 50, 000. 00 2, 000 Joanne Brown 48, 000. 00 2, 000 Donald Brown 45, 000. 00 2, 000 Paula Adams 45, 000. 00 2, 000 Editing without the dollar sign but with the comma inserted. Leading zeros do not display. SQL> SELECT name, TO_CHAR(salary, '99999. 99'), TO_CHAR(bonus, '0, 009') 2 FROM first_pay; NAME TO_CHAR(S TO_CHA ---------- -----Linda Costa 45000. 00 1, 000 John Davidson 40000. 00 1, 500 Susan Ash 25000. 00 0, 500 Stephen York 42000. 00 2, 000 Richard Jones 50000. 00 2, 000 Joanne Brown 48000. 00 2, 000 Donald Brown 45000. 00 2, 000 Paula Adams 45000. 00 2, 000 There is no comma specified for salary and bonus has leading zeros being displayed.

Conversion functions SQL> SELECT TO_CHAR(-567, '999 MI'), TO_CHAR(-0123, '9, 999 PR') 2 FROM dual; TO_CHAR ------567<123> MI prints the minus sign at the end and PR encloses the negative data in <…>. Note that if the data is not negative there is no special indication. SQL> SELECT TO_CHAR(1243, '9999 MI'), TO_CHAR(345, '999 PR') 2 FROM dual; TO_CH -----1243 345

Conversion functions SQL> DESC first_pay; Name Null? ----------------PAY_ID NAME JOBCODE STARTDATE SALARY BONUS SQL> SELECT pay_id, TO_NUMBER(pay_id) 2 FROM first_pay; PAY_ TO_NUMBER(PAY_ID) ----------1111 2222 3333 4444 5555 6666 7777 8888 In the first example, I converted pay_id to a number - you can see it aligned to the right of the field. In the second example, I compared the converted pay_id to a number. SQL> SELECT * 2 FROM first_pay 3 WHERE TO_NUMBER(pay_id) = 2222; PAY_ NAME JO STARTDATE SALARY BONUS ------------ ----2222 John Davidson IN 25 -SEP-92 40000 1500 Type ---VARCHAR 2(4) VARCHAR 2(20) CHAR(2) DATE NUMBER(9, 2) NUMBER(5)

Conversion functions SQL> SELECT * FROM first_pay; PAY_ ---1111 2222 3333 4444 5555 6666 7777 8888 NAME ----------Linda Costa John Davidson Susan Ash Stephen York Richard Jones Joanne Brown Donald Brown Paula Adams 8 rows selected. JO -CI IN AP CM CI IN STARTDATE SALARY BONUS ---------15 -JAN-97 45000 1000 25 -SEP-92 40000 1500 05 -FEB-00 25000 500 03 -JUL-97 42000 30 -OCT-92 50000 2000 18 -AUG-94 48000 2000 05 -NOV-99 45000 2000 12 -DEC-98 45000 2000 Since the system date is in 2000, the assumption is that I am looking for dates greater than June 15, 2097. Only one meets this criteria. The last two dates have 19 as their first two digits. SQL> SELECT * 2 FROM first_pay 3 WHERE startdate > TO_DATE('15 -JUN-97'); PAY_ NAME JO STARTDATE SALARY BONUS ------------ ----4444 Stephen York CM 03 -JUL-97 42000

Conversion functions SQL> SELECT name, yrgoal, NVL(yrgoal, 0) 2 FROM donor; NAME YRGOAL NVL(YRGOAL, 0) ------------Stephen Daniels 500 Jennifer Ames 400 Carl Hersey 0 Susan Ash 100 Nancy Taylor 50 50 Robert Brooks 50 50 SQL> SELECT name, yrgoal, NVL(yrgoal, -1000) 2 FROM donor; NAME YRGOAL NVL(YRGOAL, -1000) ------------Stephen Daniels 500 Jennifer Ames 400 Carl Hersey -1000 Susan Ash 100 Nancy Taylor 50 50 Robert Brooks 50 50 In one example, I displayed null and in the other example I displayed -1000 in the column where the null value occurred.