Oracle SQL Tutorials – Chapter 3 (Part 2 of 3)

NUMERIC FUNCTIONS

ROUND

• Let’s try some different usages of the ROUND function.

SELECT

ROUND (45.923),

ROUND (45.943, 1),

ROUND (45.924, 2),

ROUND (44.923, -1),

ROUND (55.923, -2)

FROM DUAL;

TRUNC

• Let’s try some different usages of the TRUNC function.

SELECT

TRUNC (45.923),

TRUNC (45.943, 1),

TRUNC (45.953, 2),

TRUNC (45.923, -1),

TRUNC (45.923, -2)

FROM DUAL;

FLOOR

• Here’s the usage of the FLOOR function.

SELECT FLOOR(46.993)

FROM DUAL;

MOD

• Here’s the usage of the MOD function

SELECT MOD(1245,10)

FROM dual;

SIGN

• The usage of the SIGN is like below.

SELECT SIGN(15),

SIGN(-15)

FROM dual;

DATE FUNCTIONS

• Let’s see how to use MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC functions.

SELECT

MONTHS_BETWEEN (TO_DATE ('01.08.2013', 'dd.mm.yyyy'),

TO_DATE ('01.01.2013', 'dd.mm.yyyy'))"Months Between",

SYSDATE "Today",

ADD_MONTHS (SYSDATE, 5) "Add Months",

NEXT_DAY (SYSDATE, 'MONDAY') "Next Monday",

LAST_DAY (SYSDATE) "Last Day of this month",

ROUND (SYSDATE - 60, 'MONTH') r1,

ROUND (SYSDATE, 'YEAR') r2,

TRUNC (SYSDATE, 'MONTH') t1,

TRUNC (SYSDATE, 'YEAR') t2

FROM DUAL;

ARITHMETIC OPERATIONS WITH DATES

Date + Number = Date : It adds days to date, as entered value.

Date – Number = Date : It subtracts days from date , as entered value.

Date – Date = Number of days : It gives the number of days between two dates.

Date + ( Number/24) = Date : It adds hours as entered value to the date.

• Let’s see how to use these operations.

SELECT

first_name,

last_name,

hire_date,

hire_date - 7,

hire_date + 1 / 24

FROM hr.employees

WHERE department_id = 90;