How to Get the Current Date in Oracle

Problem:

You want to get the current date (without the time) in Oracle.

Solution 1 (if you don't mind the zeros as the time):

SELECT
  TRUNC(CURRENT_DATE) AS current_date
FROM dual;

For example, if you were to run this query on June 16, 2021, the result table would look like this:

Discussion:

Oracle does not have a data type that stores only the date without the time. But if you don't mind the zeros in place of the time, you can simply store a date in a DATE datatype (which stores both the date and the time in Oracle). To get the current date and time as a DATE value, you can simply use CURRENT_DATE. To get rid of the time (and have zeros instead), you need to truncate the date:

SELECT
  TRUNC(CURRENT_DATE) AS current_date
FROM dual;

Solution 2 (if you want just the date and not zeros in place of the time):

SELECT
  TO_CHAR(CURRENT_DATE, 'yyyy-MM-dd') AS current_date
FROM dual;

For example, If you were to run this query on June 16, 2021, the result table would look like this:

Discussion:

To get a text of VARCHAR2 type that stores a date, you can use the TO_CHAR() function in Oracle. You need to specify the date/time stamp to be converted as the first argument, and in the format in which you want to store the date as the second argument.

Since you want the current date, the first argument is CURRENT_DATE. The second argument is your format choice. For example, 'yyyy-MM-dd' stands for 'year-month-day', where the month is numeric rather than text. The table below presents some example date formats and the respective output for the date June 16, 2021.

You can read more about date formats in Oracle in the Oracle documentation here.