Get Only Date Without Time in Oracle

Get only date without time in Oracle

Usually one would simply truncate the datetime with TRUNC:

TRUNC(pa.fromdate)

This removes the time part from the datetime, so you get the mere date. Then in your application layer, you would care about how to display it.

For example you have a GUI with a grid. The grid displays the dates according to the user's system settings (e.g. Windows region settings), but the grid knows it's dates and can sort accordingly. For this to happen, you'd fill the grid with dates, not with strings representing a date.

If you want a fixed string format (e.g. in order to write into a file), you can use TO_CHAR instead:

TO_CHAR(pa.fromdate, 'dd.mm.yyyy')

Oracle select date without time and keep date as data type

In Oracle there is no date data type that has only a year-month-day component.

The DATE data type is stored internally as 7- or 8-bytes which always has year (2-bytes), month (1-byte), day (1-byte), hour (1-byte), minute (1-byte) and second (1-byte).

The TIMESTAMP data type also has fractional seconds (and can also have a time zone).

Can I try something else?

No, you either use a VARCHAR2 string or use a DATE or TIMESTAMP and accept that it has a time component.

Select from table by knowing only date without time (ORACLE)

DATE is a reserved keyword in Oracle, so I'm using column-name your_date instead.

If you have an index on your_date, I would use

WHERE your_date >= TO_DATE('2010-08-03', 'YYYY-MM-DD')
  AND your_date <  TO_DATE('2010-08-04', 'YYYY-MM-DD')

or BETWEEN:

WHERE your_date BETWEEN TO_DATE('2010-08-03', 'YYYY-MM-DD')
                    AND TO_DATE('2010-08-03 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

If there is no index or if there are not too many records

WHERE TRUNC(your_date) = TO_DATE('2010-08-03', 'YYYY-MM-DD')

should be sufficient. TRUNC without parameter removes hours, minutes and seconds from a DATE.

---

If performance really matters, consider putting a Function Based Index on that column:

CREATE INDEX trunc_date_idx ON t1(TRUNC(your_date));

Date type without time in Oracle

The best solution would be to:

1. remove all times from your DATE column (update yourtable set yourdatecolumn = trunc(yourdatecolumn))

2. ensure that all future dates contain no time part by placing a check constraint on the column by using check (yourdatecolumn = trunc(yourdatecolumn))

3. adjust all your INSERT and UPDATE statements or -if you're lucky- adjust your API, to only insert TRUNCed dates.

The easiest solution would be to:

1. (Optionally) remove all times from your DATE column.

2. Create a before row insert or update database trigger that sets :new.yourdatecolumn := trunc(:new.yourdatecolumn);

oracle SQL how to remove time from date

When you convert your string to a date you need to match the date mask to the format in the string. This includes a time element, which you need to remove with truncation:

select 
    p1.PA_VALUE as StartDate,
    p2.PA_VALUE as EndDate
from WP_Work p 
LEFT JOIN PARAMETER p1 on p1.WP_ID=p.WP_ID AND p1.NAME = 'StartDate'
LEFT JOIN PARAMETER p2 on p2.WP_ID=p.WP_ID AND p2.NAME = 'Date_To'
WHERE p.TYPE = 'EventManagement2'
AND trunc(TO_DATE(p1.PA_VALUE, 'DD-MM-YYYY HH24:MI')) >= TO_DATE('25/10/2012', 'DD/MM/YYYY')
AND trunc(TO_DATE(p2.PA_VALUE, 'DD-MM-YYYY HH24:MI')) <= TO_DATE('26/10/2012', 'DD/MM/YYYY')

---

Outside the scope of the question, but storing dates as strings is bad practice, and storing date times is even worse.

1. We need to convert the strings to dates in order to do any form of date processing (arithmetic, interval assessment, etc) on them
2. Strings offer no guarantees regarding format, so we run the risk of date corruption crashing our code. We can defend against this by employing VALIDATE_CONVERSION() (available since 12c, find out more ) but it's still a PITN
3. Using non-standard datatypes makes it harder to reason about the data model and the code we build over it.

JDBC - How to Retrieve only date without time (ORACLE 12c)

Since you're retrieving the data as a string, you could just add one more step and format it

String sql = "SELECT E.EMP_NAME, D.DEPT_NAME, ES.ROLE, TO_CHAR(ES.DATE_OF_APPOINTMENT,'YYYY-MM-DD') AS DATE_OF_APPOINTMENT, S.SALARY "
            + "FROM EMPLOYEE E, DEPARTMENT D, DEPARTMENT_ROLE DR, EMPLOYEE_SERVICE ES, SALARY S "
            + "WHERE E.EMP_ID = ES.EMP_ID AND E.EMP_ID=S.EMP_ID AND D.DEPT_ID=DR.DEPT_ID AND DR.ROLE=ES.ROLE "
            + "AND E.EMP_ID='"+id+"'";

Update only date without modifying time

You need to use midnight on the current day, and add on the time part from the original value:

trunc(sysdate) + (last_updated_product - trunc(last_updated_product))

trunc() gives you the date with the time component set to 00:00:00, so date - trunc(date) gives you just the original time component, as a number (fraction of a day) as per the datetime arithmetic rules. That number can then be added on to midnight today.

Not sure if you're actually updating the table or just doing this in a query, but it's the same calculation either way.

---

Related Topics