Hi everyone, in this tutorial we will look the Date functions. Date functions in SQL Language are given below.
Used to add months to the date value that you type as a result of the SQL query
select * from student where surname='Federer';
select first_name,surname,Add_Months(birth_day,1)from student where surname='Federer';
Used to find the month difference between two dates.
select months_between('08-04-2019','08-01-2019') "Between Months" from dual;
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;
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.
select round (to_date(’18-May-2019′),’MM’) as “Month Rounding” from dual;
Rounded May to June as shown in the picture.
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.
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.
select first_name, to_char(birth_day, 'DD Month YYYY') BIRTH_DAY from Student;
Used to convert values from null to another value in SQL Query result.
select * from student;
Select first_name,surname,NVL(CASH,0) from student;
It is used to average the given values.
select first_name,last_name,salary from hr.employees;
select avg(salary) from HR.employees;
Used to find the number of rows in a table.
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;
Used to find the maximum / smallest equivalents of the given values.
select max(salary) "Max Salary" from HR.employees;
select min(salary) "Min Salary" from HR.employees;
Used to find the sum of the values in the entered column.
select SUM(salary) "Sum Salary" from HR.employees;
See you in the next article..
598 views last month, 1 views today