In my previous article I have provided the different date functions and its real life industry examples. In this article i would like to give more information about one of the most used function in real life. I would like to give more information about Oracle DATEDIFF function examples and queries. The DATEDIFF function is really useful function in oracle and PLSQL programs.
What we will cover in this article ?
- Oracle DATEDIFF function Syntax
- Oracle DATEDIFF function multiple examples and scenarios
Oracle DATEDIFF function Examples and Syntax :
In this section i want to give the syntax and examples of Oracle DATEDIFF function.
Syntax :
DATEDIFF (Date_part(It will be year,quarter,month,day,hour etc.,DATE_1,DATE_2)
The DATE_1 and DATE_2 are dates which we require to compare.
Real life industry examples :
In this section i would like to give different examples of DATEDIFF function.
Scenario 1 : Date_part as Days. When we want to calculate difference between dates in days.
This is the most used scenario where we require to calculate difference of dates in “days”.
Select DATEDIFF(day,’2020-08-19′,’2020-08-22′) As “Date_Difference” from Dual;
Output :
Date_Difference
3
Scenario 2 : Date_part as Hour. When we want to calculate difference between dates in hours.
To execute this scenario we can use following query,
SELECT DATEDIFF(HOUR,Sysdate-1Sysdate) as “Hours” from dual;
Output :
Hours
24
Scenario 3 : When your date format is different
You can use different date format to calculate difference of dates.
Query :
Select DATEDIFF(day,’20200604′,’20200805′) as “Date_difference” from Dual;
Output :
Date_difference
91
You can use only two digits as years as well,
Query :
Select DATEDIFF(day,’200604′,’200805′) as “Date_difference” from Dual;
Output :
Date_difference
91
You will get following error if the date format is wrong,
Error :
Msg 443, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.
Scenario 4 : Calculate the Difference in week
You can calculate the difference between dates in week. You do not need to do any arithmetic operations to do that.
Same Calendar days of Week Example:
SELECT DATEDIFF(WEEK, ‘2020-08-19’, ‘2020-08-22’) as “Week_diff” from Dual;
Output :
Week_Diff
0
Same days in different week Example:
SELECT DATEDIFF(WEEK, ‘2020-08-19’, ‘2020-08-26’) as “Week_diff” from Dual;
Output :
Week_Diff
1
Scenario 5 : Calculate the Difference in Year
You can calculate date difference in year.
Example 1 :
Select DATEDIFF(Year, ‘2020-01-23’, ‘2020-04-23’) As “Result_year” from Dual;
Output :
Result_year
1
Example 2 : Date Difference in year
Select DATEDIFF(Year, ‘2020-01-01’, ‘2019-12-31’) “Year_result” from Dual;
Output :
Year_result
1
Scenario 6 : We require to use different default values
SELECT DATEDIFF(MONTH,’1900-01-01′ , GETDATE()) As “Difference_month” from Dual;
Output :
Difference_Month
1436
Scenario 7 : Use of day and day of year
You can calculate the difference of date in days or you can also check day of year.
Select DATEDIFF(dayofyear, ‘2020-01-01’, ‘2020-02-02’) as “day_of_year” from dual;
Output :
Day_of_year
32
You can calculate the difference of date in days or you can also check day of year.
Select DATEDIFF(day, ‘2020-01-01’, ‘2020-02-02’) as “day” from dual;
Output :
day
32
Scenario 8 : DATEDIFF with wrong output
If you give or shuffle the input values for functions it will produce wrong output.
Select DATEDIFF(year,’2020-05-18′, ‘2020-01-01’) from dual;
Output :
Wrong_Output
1
Correct query :
Select DATEDIFF(month,’2020-01-01′, ‘2020-08-05’) as “right_output” from dual;
Output :
right_output
8
I hope these examples help you getting information about Oracle DATEDIFF function examples . If you like this article or if you have any issues with the same kindly comment in comments section.