CODEWITHSUNDEEP
sqloracle-database
Manish Champaneri
Manish Champaneri

Reputation: 652

Compare date with current_date in Oracle

I can not get what is wrong in this query ( ORACLE QUERY )

SELECT *
FROM HR.CUSTOMER C
dual
WHERE CREATED_AT = current_date
;

I am getting this error

ORA-00933: SQL command not properly ended

Upvotes: 6

Views: 26631

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

There is a dual too many in your query.

Moreover, in Oracle current_date is not the current date for the database, but the current datetime of your session. While your database server may be in a timezone where it is currently 11 p.m., it may be next day 3 a.m. already on your PC. Whenever you spot current_date in an Oracle query it is very likely wrong.

In Oracle use sysdate for now and trunc(sysdate) for today.

select * 
from hr.customer 
where created_at = trunc(sysdate);

Upvotes: 7

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

Since you already know the table and wanted to get all the columns from it, you need not use dual

The DUAL table is a special one-row, one-column table present by default in Oracle and other database installations. In Oracle, the table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'. It is suitable for use in selecting a pseudo column such as SYSDATE or USER.

You may just use the following query instead

SELECT *
  FROM HR.CUSTOMER
 WHERE CREATED_AT = current_date;

Upvotes: 1

Related Questions