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';

blank

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

blank

 

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;

blank

 

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;

blank

 

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;

blank

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;

blank

 

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;

blank

 

Nvl Function

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

Example:

select * from student;

blank

 

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

blank

AVG Function

It is used to average the given values.

Example:

select first_name,last_name,salary from hr.employees;

blank

 

select avg(salary) from HR.employees;

blank

 

Count Function

Used to find the number of rows in a table.

Example:

select first_name from hr.employees;

blank

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

select count(*) from hr.employees;

blank

Max/Min Function

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

Example:

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

blank

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

blank

 

Sum Function

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

Example:

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

blank

 

See you in the next article..

 182 views last month,  6 views today

About Deniz Parlak

blank
Hi, i’m Security Data Scientist & Data Engineer at My Security Analytics. I have experienced Advance Python, Machine Learning and Big Data tools. Also i worked Oracle Database Administration, Migration and upgrade projects. For your questions parlak.deniss@gmail.com

Check Also

blank

Create Table ( CTAS ) and Data Types, LOB Data Types in Oracle SQL | Oracle SQL Tutorials -27

Hi, I will explain Create Table ( CTAS ) and Data Types in Oracle SQL in …

Leave a Reply