Oracle Introduction to SQL Tutorial – 5

Hi everyone, in this tutorial we will look the Date functions.  Date functions in SQL Language are given below.

ADD_MONTHS Function

Used to add months to the date value that you type as a result of the SQL query

Example:

select * from student where surname='Federer';

select first_name,surname,Add_Months(birth_day,1)from student where surname='Federer';

Months_Between Functions

Used to find the month difference between two dates.

Example:

select months_between('08-04-2019','08-01-2019') "Between Months" from dual;

Next_Day Function

It adds the date of the week that you give to the date value that you give in SQL Query and gives the corresponding date information.

Note: The language definition of the day of the week may vary according to the “NLS_LANGUAGE” parameter.

select next_day('20-Jan-2019','Monday') as "Next Monday" from dual;

Round Function

It is used to round the value you give in the SQL Query to minute / hour / day / week / month.

• DD: Day rounding.
• W:   Week rounding.
• MM: Month rounding.
• HH: Hour rounding.
• MI: Mınute rounding.

Example:

select round (to_date(’18-May-2019′),’MM’) as “Month Rounding” from dual;

Rounded May to June as shown in the picture.

Trunc Function

Rounds the reference date value that we provide in the SQL Query according to the rounding degree value that we will give in our query.

Example:

select TRUNC( to_date('20-May-2019'),'W') as "Trunc Week" from dual;

To_char with Dates

When using the date format,

• Must be single-quoted and case-sensitive.
• It must contain the current date format.
• The format component is separated by a comma from the date value.

Example:

select first_name, to_char(birth_day, 'DD Month YYYY') BIRTH_DAY from Student;

Nvl Function

Used to convert values from null to another value in SQL Query result.

Example:

select * from student;

Select first_name,surname,NVL(CASH,0) from student;

AVG Function

It is used to average the given values.

Example:

select first_name,last_name,salary from hr.employees;

select avg(salary) from HR.employees;

Count Function

Used to find the number of rows in a table.

Example:

select first_name from hr.employees;

There’s more than 25 lines in the image. Let’s see how many records there are

select count(*) from hr.employees;

Max/Min Function

Used to find the maximum / smallest equivalents of the given values.

Example:

select max(salary) "Max Salary" from HR.employees;

select min(salary) "Min Salary" from HR.employees;

Sum Function

Used to find the sum of the values in the entered column.

Example:

select SUM(salary) "Sum Salary" from HR.employees;

See you in the next article..