CODEWITHSUNDEEP
sqloracle-database
Slim
Slim

Reputation: 1744

oracle SQL how to remove time from date

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

Upvotes: 57

Views: 304548

Answers (6)

Nusret Onur Ayduman
Nusret Onur Ayduman

Reputation: 21

there is also extended usage like this:

  WITH dates AS (   
  SELECT date'2015-01-01' d FROM dual union   
  SELECT date'2015-01-10' d FROM dual union   
  SELECT date'2015-02-01' d FROM dual union   
  SELECT timestamp'2015-03-03 23:45:00' d FROM dual union   
  SELECT timestamp'2015-04-11 12:34:56' d FROM dual    
)   
SELECT d "Original Date",   
       trunc(d) "Nearest Day, Time Removed",   
       trunc(d, 'ww') "Nearest Week", 
       trunc(d, 'iw') "Start of Week",   
       trunc(d, 'mm') "Start of Month",   
       trunc(d, 'year') "Start of Year"   
FROM dates;

Oracle Offical Help Page

Upvotes: 0

APC
APC

Reputation: 146209

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.

Upvotes: 27

it_iz_code
it_iz_code

Reputation: 43

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

Upvotes: 1

Jar Yit
Jar Yit

Reputation: 985

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

Upvotes: 10

Pavel
Pavel

Reputation: 151

Try

SELECT to_char(p1.PA_VALUE,'DD/MM/YYYY') as StartDate,
       to_char(p2.PA_VALUE,'DD/MM/YYYY') as EndDate
   ...

Upvotes: 7

Habib
Habib

Reputation: 223217

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.

Upvotes: 73

Related Questions