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)
Subscribe
For further actions, you may consider blocking this person and/or reporting abuse
Top comments (0)