What is Oracle DATEDIFF function with examples?

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 ?

1. Oracle DATEDIFF function Syntax
2. 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.