Categories: SQL Tutorials

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.

Amit S

Oracle Consultant with vast experience in Oracle BI and PL/SQL Development. Amiet is the admin head of this website who contributes by preparing tutorials and articles related to database technologies. He is responsible to manage the content and front-end of the website.

Recent Posts

What is Root Cause Analysis (RCA) With real examples

In my previous article I have given details about application support engineer day to day…

4 weeks ago

Application Support Engineer Day to day responsibilities

In my previous articles I have given the roles and responsibilities of L1,L2 and L3…

1 month ago

What is mean by SLA ( Service Level Agreement) with Examples?

In my previous articles i have given the hierarchy of production support in real company…

1 month ago

What is Production support Hierarchy in organization?

In this article i would like to provide information about production support organization structure or…

1 month ago

What are roles and responsibilities for L3 Support Engineer?

In my previous article I have given roles for L1 and L2 support engineer with…

1 month ago

What are roles and responsibilities of L2 Engineer?

I have started this new series of how to become application support engineer. This article…

1 month ago