get value from table where date difference is more then certain value

Question

I have a table EMPLOYEE_STATUS, with column SUBMIT_DATE values as 11-05-18 11:27:34.086000000 AM and UPDATED_ON values as 11-06-18 12:55:22.064000000 PM which is basically current date.

I need a sql query for oracle database to find those rows where difference in days between submited date and updated on or current date is 30 days or more. I tried DATEDIFF function but couldnt able to make it work.

I have tried the following (which does not work):

select * from EMPLOYEE_STATUS e 
where DATEDIFF(month, '12-06-18 03:34:09.448000000 PM', e.SUBMIT_DATE) >=1

select * from EMPLOYEE_STATUS e where DATEDIFF(month, '12-06-18 03:34:09.448000000 PM', e.SUBMIT_DATE) >=1; its not working. — shrikant.sharma, Jun 12 '18 at 09:57
`SELECT * FROM employee_status WHERE submit_date >= TIMESTAMP '2012-06-18 15:34:09.448' + INTERVAL '30' DAY` — MT0, Jun 12 '18 at 10:23

Jakob Busk Sørensen · Answer 1 · 2018-06-12T10:21:00.797

-1

The function DATEDIFF() is not included in Oracle SQL server (as mentioned in this post). Instead you simply subtract the dates from each other, like this:

SELECT (11-06-18 12:55:22.064000000 PM) - (11-05-18 11:27:34.086000000 AM);

Which returns the difference in days. So you have to compensate for that (in your example, you are looking for a difference of a month, which could be anything between 28 and 31 days).

For those using MySQL or SQL Server, you can do it like this:

SELECT DATEDIFF(month, '2018-05-11 11:27:34', '2018-06-11 12:55:22') AS DateDiff;

edited Jun 12 '18 at 10:21

answered Jun 12 '18 at 10:03

Jakob Busk Sørensen

5,599
7
44
96

i tried above and output is ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause: *Action: Error at Line: 1 Column: 80 – shrikant.sharma Jun 12 '18 at 10:13
`DATEDIFF` is not an Oracle function – MT0 Jun 12 '18 at 10:17
@shrikant.sharma thank you for pointing that out. I am used to working in SQL Server. I have edited my answer. – Jakob Busk Sørensen Jun 12 '18 at 10:21
ok , but it didnt work , and how to add colum name rather then having hard coded date. – shrikant.sharma Jun 12 '18 at 10:27
@shrikant.sharma you just replace one of the values (the first one in your case) with the column name. – Jakob Busk Sørensen Jun 12 '18 at 12:26
ok this works for me select * from EMPLOYEE_STATUS where trunc(bo.SUBMIT_DATE) – shrikant.sharma Jun 12 '18 at 12:29

get value from table where date difference is more then certain value

1 Answers1