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    gt   TO DATE  25 10 2012    DD MM YYYY   AND TO DATE p2 PA VALUE   DD MM YYYY    lt   TO DATE  26 10 2012    DD MM YYYY     Is there a way to do this   EDIT1  the PA VALUE column is  VARCHAR2

User · Answer

If your column with DATE datatype has value like below : -

value in column : 10-NOV-2005 06:31:00

Then, You can Use TRUNC function in select query to convert your date-time value to only date like - DD/MM/YYYY or DD-MON-YYYY

select TRUNC(column_1) from table1;

result : 10-NOV-2005

You will see above result - Provided that NLS_DATE_FORMAT is set as like below :-

Alter session NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

User · Answer

Try  SELECT to char p1 PA VALUE  DD MM YYYY   as StartDate         to char p2 PA VALUE  DD MM YYYY   as EndDate

User · Answer

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     gt   TO DATE  25 10 2012    DD MM YYYY   AND trunc TO DATE p2 PA VALUE   DD-MM-YYYY HH24 MI     lt   TO DATE  26 10 2012    DD MM YYYY

User · Answer

You can use TRUNC on DateTime to remove Time part of the DateTime  So your where clause can be    AND TRUNC p1 PA VALUE   gt   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

User · Answer

We can use TRUNC function in Oracle DB  Here is an example   SELECT TRUNC TO DATE  01 Jan 2018 08 00 00   DD-MON-YYYY HH24 MI SS    FROM DUAL   Output      1 1 2018

[sql] oracle SQL how to remove time from date

Examples related to sql

Examples related to oracle