TO_DATE function Oracle SQL

Question

I am trying to select data from a certain date from range, but not within a certain set of parameters.

Currently:

SELECT external_reporting_id AS asin,
       last_updated          AS "Received"
  FROM transfer_delivery_items
 WHERE last_updated >= TO_DATE(sysdate, 'DD-MON-YY') - 13
 ORDER BY last_updated DESC;

But I would want to see everything NOT in that date range.
I have tried >= TO_DATE(sysdate, 'DD-MON-YY')-13, but it doesn't look like it is an approved format, or even possible if anything can lend me a hand that'd be great.

`to_date()` converts a **string** to a **date**. `sysdate` *is* already a date. So it's *totally* useless to apply `to_date()` on `date` value to `convert` it to a `date` value. It's actually even worse: because this way the `date` value `sysdate` gets converted to a string value first (subject to implicit NLS settings) just to be converted back to a `date` again. — , Jan 13 '14 at 18:30
I was under the impression I had to to convert to the date format. When I remove sysdate, I receive error, and when I remove sysdate, and 'DD-MON-YYYY' I receive errors. So, I've always used them on this db. — Nomad, Jan 13 '14 at 18:34
A `date` does not have a "format". So there is no need to "convert" it. In your case `last_updated >= sysdate - 13` should work just fine. Probably better to use `trunc(last_updated) >= trunc(sysdate) - 14` to ignore the time part of the `date` values. — , Jan 13 '14 at 18:46
Would this work?
select external_reporting_id ASIN, last_updated as "Received" from transfer_delivery_items where last_updated <= trunc(sysdate)-13 or last_updated >= trunc(sysdate)+13 order by last_updated desc; — Nomad, Jan 13 '14 at 19:02

Brian McGinity · Accepted Answer · 2014-01-21T01:22:08.990

sysdate is already a date, so if you want everything 13 days prior do:

select external_reporting_id ASIN, last_updated as "Received" 
  from transfer_delivery_items
 where last_updated >= trunc(sysdate) -13
 order by last_updated desc;

The trunc(sysdate) will remove the time from the date, to encompass all records 13 days prior.

Note: I would not do

trunc(last_updated) >= trunc(sysdate)

because last_updaetd is probably an indexed column and trunc(last_update) will bypass that index. (unless there is a function index on trunc(last_updated).

Update:

To get the block of dates 13 days prior and no more than 13 days into the future do:

select external_reporting_id ASIN, last_updated as "Received" 
  from transfer_delivery_items
 where last_updated >= trunc(sysdate) -13
   and last_updated <= trunc(sysdate) +13
 order by last_updated desc;

What if I wanted everything AT least 13 days prior, and at least 13 days ahead. And black out today +13 and today -13? — Nomad, Jan 20 '14 at 23:24

score 0 · Answer 2 · answered Jan 13 '14 at 18:27

0

It think you can NOT the BETWEEN: http://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions011.htm#SQLRF52147

I don't have an Oracle instance available to me or I'd test this out. I'm pretty sure it will work.

answered Jan 13 '14 at 18:27

Bob Kuhar

10,838
11
62
115

Wouldn't that specify me to set a date range? Or, what dates I want? – Nomad Jan 13 '14 at 18:30
When I do this : where last_updated not between trunc(sysdate) and trunc(sysdate)-13 I still receive the data between the dates hmm.. – Nomad Jan 13 '14 at 19:07
I recommend you forget about sysdate, trunc, and the like for now. I think you aren't working with the DATE values you think you are working with. BETWEEN has always worked for me. – Bob Kuhar Jan 13 '14 at 23:59

TO_DATE function Oracle SQL

2 Answers2

Update: