Number Function
- Round
- Trunc
- Mod
- Ceil
- Floor
- Abs
- Sign
Round
- If there is number => 5 after the decimal point , it will round off to the next number otherwise it will remain the same.
select round(67.34) from dual;
select round(67.49) from dual;
select round(67.57) from dual;
Trunc
- It wont see any number after decimal.
- Trunc is used only with the NUMBERS.
- Lets do one more operation with trunc --> trunc(56.89898,2) means it will take the two digit after the decimal.
select trunc(67.7846434) from dual;
select trunc(67.10001) from dual;
select trunc(67.011234) from dual;
select sysdate from dual; --> it will display time also
select trunc(sysdate) from dual;
Mod
- It will give the remainder.
select mod(10,7) from dual;
select mod(61,10) from dual;
select mod(6,2) from dual;
Ceil
- It will go to the next number, whatever may the number.
select ceil(67.7846434) from dual;
select ceil(67.00001) from dual;
select ceil(67.011234) from dual;
Floor
- It will crop the decimal.
- Then what is the use of Trunc ? In trunc we can use, how many decimal can be included. Hope you remember.
select floor(67.7846434) from dual;
select floor(67.00001) from dual;
select floor(67.011234) from dual;
select floor(67.7846434),trunc(67.7846434,1) from dual;
select floor(67.00001),trunc(67.00001) from dual;
select floor(67.011234) , trunc(67.011234,0) from dual;
Abs
- Only + & - are allowed.
- It will remove the sign.
- There is one more function similar to this sign i.e SIGN, it will give 1 & -1.
select abs(67.7846434), abs(-67.7846434), abs(10-15), sign(-10) , sign(+67.89) from dual;
Date Function
- add_months
- months_between
- next_day
- last_day
add_months
- if you want to add MONTHS then this function is used.
select sysdate, sysdate+2 , sysdate+20 , sysdate-10 from dual; -- Days increases
select sysdate from dual;
select add_months(sysdate,1) from dual;
select add_months('01-DEC-2024',1) from dual;
select add_months(sysdate,-1) from dual; -- months increases
select * from hr.employees where hire_date < add_months(sysdate,-276) -- hired before 23 years , 276 is the month
months_between
- Displays the no of months , sometime it will give in negative number that means subtraction happens between months Jan is 1 and Dec is 12.
- Even the date will be considered.
-- select months_between((01-01-20024), (01-12-2024)) from dual;
-- select months_between((01-jan-2024), (01-dec-2024)) from dual;
select months_between(('01-JAN-2024'), ('01-DEC-2024')) , months_between(('01-DEC-2024'), ('01-jan-2024')) from dual;
select months_between(('17-JAN-2024'), ('01-DEC-2024')) , months_between(('01-DEC-2024'), ('25-jan-2024')) from dual;
- If you don't like the decimal , what you can use ? For sure I forgot . Can't remember very quickly . After reading my notes , found its "TRUNC or FLOOR".
- Find my age ? How ?
select months_between(('01-DEC-2024'), ('01-DEC-1989'))/12 from dual;
select months_between(('01-DEC-2024'), ('01-DEC-1989'))/12 AS AGE from dual;
next_day
- Also will order you can specify.
- Sunday --> 1 , Saturday --> 7
select sysdate from dual ;
select next_day(sysdate,'sunday') from dual;
select next_day(sysdate,1) from dual;
last_day
- To find the last day of the month.
select last_day(sysdate) from dual;
select last_day('01-feb-2024') from dual;
General
- greatest
- least
- distinct
- unique
- case
- decode
- concat
- ||
Greatest
select 12,67,98,90,100 from dual;
select greatest(12,67,98,90,100) , least (12,67,98,90,100) from dual;
Least
select 12,67,98,90,100 from dual;
select greatest(12,67,98,90,100) , least (12,67,98,90,100) from dual;
Distinct & Unique
- It will check the duplicate.
- Both will be same .
select unique FIRST_NAME , last_name from hr.employees;
select distinct FIRST_NAME , last_name from hr.employees;
Concat & ||
select concat(concat(FIRST_NAME,LAST_NAME),salary) from hr.employees;
select FIRST_NAME || LAST_NAME ||' '|| salary from hr.employees;
Note
- In the Date function --> only months_between --> it returns number.
Top comments (0)