How to use GETDATE() in Oracle to find number of days between Current Date and some specified date?

Question

Suppose I have a table called projects and it has columns as projectid, startdate and enddate. The startdate of each project is specified already and the enddate is specified only for projects that are already finished. For ongoing projects, the enddate is null. We have to perform this task :

Find the project ID and duration of each project. If the project has not finished, report its execution time as of now. [Hint: Getdate() gives current date]

For this I wrote this code,

select projectid,
CASE
when enddate is null then GETDATE() - startdate
else enddate - startdate
END
as "duration" from projects;

But this is giving me

ORA-00904: "GETDATE": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 113 Column: 27

The line 113, column 27 is the position of GETDATE().

So what exactly is the way to use getdate() here?

Answer 1

GETDATE() is a function from TSQL. What you want to use instead is CURRENT_DATE. Also, I improved your query a bit by using COALESCE.

select projectid,
COALESCE(enddate,CURRENT_DATE) - startdate 
as "duration" from projects;

You may want to refine the result using FLOOR() as well to get exact days.