Oracle SQL How to Remove Time from Date

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.

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')

How do I remove the date from a datetime stamp in Oracle?

You can look at the hour portion alone; as you have a timestamp you can extract the hour number:

where extract(hour from closed_date) = 9

That will find rows there time is on or after 09:00:00, and before (not including) 10:00:00.

---

Usually when looking at data in a time period like 'between 9 and 10' you don't actually want to include the upper limit, because if you were also looking at data 'between 10 and 11' then data at exactly 10:00:00 would be included in both, which probably isn't what was intended. So it's common for date/time comparisons to use >= and < instead of between; you could also do this with a string comparison which you might consider clearer:

where to_char(closed_date, 'HH24:MI:SS') >= '09:00:00'
and to_char(closed_date, 'HH24:MI:SS') < '10:00:00'

or slightly more simply

where to_char(closed_date, 'HH24') >= '09'
and to_char(closed_date, 'HH24') < '10'

which in this case, as it's a single hour, is the same as:

where to_char(closed_date, 'HH24') = '09'

but then as you are only looking at the hour part anyway, extracting that as a number simplifies it even more (IMO).

Issue in removing time stamp in PL/SQL

Try this:

trunc(effective_date_time)

It's a date, you don't need TO_DATE

When you're using TO_DATE(effective_date_time, 'format') on a DATE column, effective_date_time is converted to a char using NLS params. I suppose your NLS settings is something like 'dd/mm/yy'. That's why you get a wrong year.

A simple example:

alter session set nls_date_format = 'dd/mm/yy';
select trunc(TO_DATE (sysdate,'DD/MM/YYYY HH24:Mi:SS')) from dual;

November, 22 0014 00:00:00+0000

alter session set nls_date_format = 'dd/mm/yyyy';
select trunc(TO_DATE (sysdate,'DD/MM/YYYY HH24:Mi:SS')) from dual;

November, 22 2014 00:00:00+0000

How to remove timestamp from date without converting to character string

Don't divide by 365 as a year does not always have 365 days. Use the MONTHS_BETWEEN function and divide by 12:

Select ca.CUSTOMID,
       ca.SUBMITDATE, 
       pe.DATEARRIVED,
       MONTHS_BETWEEN( ca.SUBMITDATE, pe.DATEARRIVED )/ 12 AS Years_In_Country 
FROM   CURUM.CLASS ca
       LEFT JOIN CURUM.PERSON pe
       ON ca.CUSTOMID = pe.CUSTOMID

db<>fiddle here

How to remove timestamp from date without converting to character string

This question does not make sense. In Oracle, a DATE data type always has year, month, day, hours, minutes and seconds components and a TIMESTAMP data type has the same with optional fractional seconds and time zone components. Also, they are both binary data formats (consisting of 7 bytes for a DATE and 7 bytes for a TIMESTAMP(0) through to 20 bytes for a TIMESTAMP(9) WITH TIME ZONE) and, as such, neither have any format associated with them.

If you want to remove the "time" component from a DATE or a TIMESTAMP then you need to convert them to a different data type that can display formatted date information; that data type would be a string.

So, if you want it to be a DATE or TIMESTAMP data type then it will have a "time" component and if you don't want it to be have a "time" component then it will be a string.

If by "remove" you mean that you want to set the "time" component to midnight then just use the TRUNC function and the value will be cast to a DATE data type where the time components are at midnight. The NLS_DATE_FORMAT of the session you are using may be set to not show the time component of a DATE data type but it will still exist.

If you want to format the value without a time component then use TO_CHAR( your_column, 'YYYY-MM-DD' ) to convert it to a formatted string.

Convert datetime field to just a date field in SQL (Oracle)

Use to_char function:

SELECT DISTINCT
to_char(C.RECEIPTDATE,'DD/MM/YYYY'),
(I.CLIENTID ||' - '||PO.CLIENTNAME) AS CLIENT,
D.INVOICEID,
D.SVCFROMDATE,
D.SVCTODATE,
D.SVCCODE
FROM M_EQP_ORDERS
WHERE.....

---

Related Topics