Date And Other Functions

Dates:

  • Oracle stores data in mathematical format : country, year, month, day, hours, minutes, seconds.
  • The default date format is DD-MM-YY

 

SQL> SELECT sysdate

2  FROM DUAL;

 

Output

SYSDATE

———

25-FEB-11

 

Notes: – You can quickly test the different oracle functions by using the dual

table

 

Arithmetic with dates

Operator Result Description
date + no date Add no. of days
date – no date Subtract no. of days
date – date no. of days Subtract date from other
date  -/+  no/24 date Add subtract no. of hours

 

 

 

Student Exercise 1:

Write a query that lists the no. of days that the employee has spent in organization. Restricted this result for employees of department no. 10

 

 

Output

ENAME      DAYS SPENT

———- ———-

CLARK       9586.3727

KING        9425.3727

MILLER      9358.3727

 

 

NEXT_DAY:

  • Return the date of the day specified

NEXT_DAY(date,’<DAY>’)

 

Student Exercise 2:

Find the day for next Saturday.

 

LAST_DAY:

  • Return the date of the last date of month specified
  • LAST_DAY(<date>);

Student Exercise 3:

Find the last day of the current month.

 

ADD_MONTHS:

  • Add the value ‘n’ moth to a day where n can be a +ve or –ve integer

ADD_MONTHS(date,<number>)

Student Exercise 3:

Find thethrid month from the current month.

 

MONTH_BETWEEN:

  • It return the month between the 2 dates

MONTHS_BETWEEN(date1,date2)

 

Student Exercise 4:

Produce the output

WORK EXPERIENCE

——————————

SMITH has worked for 320.463 months with us.

…….

Round And Truncate:

ROUND (TO_DATE(’25-JUL-95’),’MONTH’)    o/p   01-AUG-95

ROUND (TO_DATE(’25-JUL-95’),’YEAR’)       o/p   01-JAN-96

TRUNC (TO_DATE(’25-JUL-95’),’MONTH’)     o/p   01-JUL-95

TRUNC (TO_DATE(’25-JUL-95’),’YEAR’)        o/p   01-JAN-95

 

Student Exercise 5:

Compare the hiredate for all employee who join on 1982. Display the empno, hiredate, and month using ROUND and TRUNC function.

Output

ENAME      HIREDATE  TRUNC(HIR ROUND(HIR

———- ——— ——— ———

MILLER     23-JAN-82 01-JAN-82 01-FEB-82

 

TO_CHAR Function with Date:

  • It is use to convert a no. or date into varchar2 string.

TO_CHAR (date,’format’)

 

YYYY Full year in number
YEAR Year spelled out
MM 2 digit value for month
MONTH Full name for month
DY 3 letter abbreviation for day
DAY Full name of day
DD Date

 

SQL> SELECT empno, TO_CHAR(hiredate,’MM/YY’) “MONTH HIRED”

2    FROM EMP;

3    WHERE eanme=’BLAKE’;

 

Output

EMPNO MONTH

——— —–

7698 05/81

 

Student Exercise 6:

Produce the following output

EMPLOYEE                HIRED ON

—————–               ——————

SMITH                        WED DECEMBER 17 1980

……..

 

 

Student Exercise 7:

Display the names and hiredate of all employees that joined on FEB 1981

 

TO_CHAR with Numbers:

 

SQL> SELECT TO_CHAR(sal,’$99,999′) “SALARY”

2  FROM EMP

3  WHERE ename=’SCOTT’;

 

Output

SALARY

——–

$3,000

 

Student Exercise 8:

Produce the following output

EMPLOYEE EARNINGS

———————————

SMITH earns     $800 salary.

ALLEN earns   $1,600 salary.

…………

 

NVL (Null Value’s Value):

 

NVL (comm, 0)

NVL (hiredate,’01-JAN-97’)

NVL (job,’no job yet’)

 

 

SQL> SELECT ename, sal, comm, (sal*12)+NVL(comm,0)

2  FROM EMP;

 

Output

ENAME            SAL      COMM (SAL*12)+NVL(COMM,0)

———- ——— ——— ——————–

SMITH            800                           9600

ALLEN           1600       300                19500

WARD            1250       500                15500

……

DECODE:

  • It simplifies language structure doing the work off a case or an if-else structure

 

Note: In if-else we use different variable but switch case we use only a single

variable

 

SQL> SELECT job, sal, DECODE(job,’ANALYST’,sal*1.1,’CLERK’,sal*1.5,’MANAGER’,sal*1.20,sal) “REVISED_SALARY”

2  FROM EMP;

 

Output

JOB             SAL REVISED_SALARY

——— ——— ————–

CLERK           800           1200

SALESMAN       1600           1600

……

 

Note: Functions can be nested in SQL

 

SQL> SELECT ename, NVL(TO_CHAR(mgr),’NO MANAGER’)

2  FROM EMP

3  WHERE mgr IS NULL;

 

Output

ENAME      NVL(TO_CHAR(MGR),’NOMANAGER’)

———- —————————————-

KING       NO MANAGER

 

LENGTH:

Returns the length of a a string.   LENGTH(<‘string’>)

Student Exercise 9:

Write a query to produce the output

NAME                                    LENGTH

———                      ————

Jones                              5

……..

 

Student Exercise 10:

List the name and salary of employees who earn more than $1500 and are in department 10 or 30. Use least no. of SQL lines. Label the column employee and the monthly salary respectively.

 

 

Output

ENAME          DEPTNO        SAL

———- ———- ———-

ALLEN              30       1600

BLAKE              30       2850

CLARK              10       2450

KING               10       5000

Student Exercise 11:

Write SQL to create the output

 

DREAM SALARIES

—————————

KING earns $5,000.00 but wants $15,000.00

……..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s