oracle SQL how to remove time from date

Question

I have a column named StartDate containing a date in this format: 03-03-2012 15:22

What I need is to convert it to date. It should be looking like this: DD/MM/YYYY

What I have tried without success is:

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 TO_DATE(p1.PA_VALUE, 'DD/MM/YYYY') >= TO_DATE('25/10/2012', 'DD/MM/YYYY') AND TO_DATE(p2.PA_VALUE, 'DD/MM/YYYY') <= TO_DATE('26/10/2012', 'DD/MM/YYYY') 

Is there a way to do this?

EDIT1: the PA_VALUE column is: VARCHAR2

Answer 1

You can use TRUNC on DateTime to remove Time part of the DateTime. So your where clause can be:

AND TRUNC(p1.PA_VALUE) >= TO_DATE('25/10/2012', 'DD/MM/YYYY') 

The TRUNCATE (datetime) function returns date with the time portion of the day truncated to the unit specified by the format model.

Answer 2

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