Between Using Sysdate 1

[Zeroanarchy]

Hi,

Having problems trying to return any values using the following method, can anyone make any suggestions?

The field value is set to date
and the date is as follows "dd/mm/yyyy"

SELECT T_RATES.TO_DATE 
FROM PRODUCTDB.T_RATES
WHERE 
	T_RATES.TO_DATE BETWEEN TO_CHAR(SYSDATE,'DD/MM/YYYY') AND TO_CHAR(SYSDATE -200,'DD/MM/YYYY')

Cheers

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[SantaMufasa]

First of all, whenever you use the BETWEEN comparison operator, you must ensure that the lesser of the two BETWEEN expressions is to the left of BETWEEN and the expression with the ending value is to the right of BETWEEN. In this case, since SYSDATE is always greater than "SYSDATE - 200" you will always get "no rows selected". (This partially explains your problem.)

Secondly, if you are translating a DATE expression into a CHARACTER string with the mask 'DD/MM/YYYY', then the date value '30/12/1923' compares greater than '27/06/2007' (since you are asking Oracle to do a character comparison instead of a DATE comparison. You are asking Oracle to compare days of the month before comparing months and years).

Third, it is dangerous to have a column name ("TO_DATE") that matches the spelling of an Oracle keyword or function: TO_DATE is an Oracle function name.

Fourth,

The field value is set to date.

By this, I presume you mean that "T_RATES.TO_DATE" is of data type, "DATE". if there is a column in the T_RATES table named TO_DATE and it is datatype DATE, then you do not need (or want) to convert that column to character as you have done. You should leave it simply as a DATE; you should not convert it TO_CHAR when comparing to other DATEs.


You should never store DATE/TIME information in Oracle in any column datatype besides DATE or TIMESTAMP (i.e., never store DATE/TIME information as VARCHAR2, CHAR, NUMBER, et cetera).

So, if the "T_RATES.TO_DATE" is of datatype DATE, then this simplified code should work just fine for you:

SELECT TO_DATE 
  FROM PRODUCTDB.T_RATES
 WHERE TO_DATE BETWEEN SYSDATE AND SYSDATE - 200;

Let us know if these observations resolve your problems.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Zeroanarchy]

Thanks David, I tried that previously and found it not to work. The field is set as Date and values do exist in the last 200 days but nothing seems to appear.

I have come up with a work around that required me to hardcode an excluded date and that seems to work just fine.

Thanks again for your help.

Cheers

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[SantaMufasa]

Silly me...After I told you to code <lesser> BETWEEN <greater>, I went ahead and copied what you had...try it this (correct) way instead:

SELECT TO_DATE 
  FROM PRODUCTDB.T_RATES
 WHERE TO_DATE BETWEEN SYSDATE - 200 AND SYSDATE;

Let us know the outcome.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]