Using dynamic date filter causes data not to return

Question

Sorry for the cryptic title, as I'm not sure how to state this question simply.

The problem is this, I have a date column, called DTDATE, the data stored in it is of DATE type. When I filter against this column using a static string such as '03/APR/17' queries return a result. Perfect.

However, I want to create a dynamic query that will return the first day in the week (Monday in this instance) and the same day last year (e.g. the Monday of this week last year). That is, comparing two sets of data year to date (YTD).

The filter would look something like this:

      DTDATE = (SYSDATE - (TO_CHAR(SYSDATE,'d')-1))
  OR 
      DTDATE = (add_months(SYSDATE,-12)-(TO_CHAR(add_months(SYSDATE,-12),'d')-1))

The problem is, the query returns no result!

IF, however, I change the query to the following, I get a result set:

      DTDATE = (SELECT SYSDATE - (TO_CHAR(SYSDATE,'d')-1) FROM DUAL)
  OR 
      DTDATE = (SELECT add_months(SYSDATE,-12)-(TO_CHAR(add_months(SYSDATE,-12),'d')-1) FROM DUAL)

You will notice I am now specifically selecting the date for each row of data returned in the filter. While this works, the cardinality of the data set goes from 14,475 in a static filter (e.g. date stated in a string), to 2,387,764!

....That's a bit of an issue for run time XD

So, my question is WHY wont the query run without selecting into DUAL? I can use SYSDATE by itself as a filter (tested).

Notes about the data: The data set is from an MIS, that loads data on a weekly basis each Monday. As such, datasets are only present from a tag date of Monday of each given week. Hence the date requirement of Monday of the given week.

Note I am using Oracle Developer 4.1.5.21 on an 11g server (we update to 12c later this month).

Answer 1

I'm not quite sure about what you are trying to do. I'm concern about all your type conversions to get what you want. You may want to simplify using TRUNC on your dates. TRUNC(SYSDATE,'D')+1 will give you Monday of the week. The 'D' paramter takes the date and gives you the first day of the week, which is Sunday, then add one. Then, you could do TRUNC(ADD_MONTHS(SYSDATE,-12),'D')+1 to get the previous year value.

Also, SYSDATE is not a datatype, it is essentially a function that returns a DATE data type. When you perform date arithmetic, like SYSDATE + 1, you'll still have a DATE datatype.

Answer 2

Okay, figured it out, leaving this here in case anyone else comes across a similar issue.

For some reason Oracle will not compare a DATE data type to a SYSDATE when it has been place through a function such as:

SYSDATE - (TO_CHAR(SYSDATE,'d')-1)

To overcome this limitation, I used the NEXT_DAY() and TO_DATE() functions to build the following query:

TO_DATE(NEXT_DAY(SYSDATE-7,'Monday'),'DD/MON/YY')

Using this method, the below filter returns a result set, and has a low cardinality only slightly higher than a static filter.

      PIP.DTDATE = TO_DATE(NEXT_DAY(SYSDATE-7,'Monday'),'DD/MON/YY')
  OR 
      PIP.DTDATE = TO_DATE(NEXT_DAY(SYSDATE-371,'Monday'),'DD/MON/YY')

If anyone has any comments about WHY oracle requires you to use TO_DATE(), please extrapolate, because this confused the daylights outta me XD