CODEWITHSUNDEEP
sqloracle-database
Cade W.
Cade W.

Reputation: 389

Oracle SQL - Variable between two dates

I cannot figure out why this code will not work. It is a bit backwards, which may be confusing me. 

SELECT * FROM 
(  
 SELECT   
 TO_CHAR(FTPRITIN.start_date, 'DD-MON-YY') start_date,
 TO_CHAR(FTPRITIN.end_date, 'DD-MON-YY') end_date 
 FROM ftpritin 
)
WHERE :VARIABLE BETWEEN start_date AND end_date

I have also tried

SELECT * FROM 
(  
 SELECT   
 TO_CHAR(FTPRITIN.start_date, 'DD-MON-YY') start_date,
 TO_CHAR(FTPRITIN.end_date, 'DD-MON-YY') end_date 
 FROM ftpritin 
)
WHERE start_date > :VARIABLE AND end_date < :VARIABLE

But it does not seem to work. The query runs but grabs data completely out of the range.

What i need is to grab data rows where the variable falls between the start_date and end_date. Any ideas? Any help is much appreciated.

ANSWER

FYI - For those who wonder why such an easy problem was so hard - Or for those who have a similar problem...

The field was a TIMESTAMP and not a date field. I figured this out by running

DESC table_name

Then i just did a quick substr to_date to fix it

to_date(substr(start_date, 1, 9))

This allowed me to filter by date

WHERE to_date(substr(start_date, 1, 9)) <= to_date(:VARIABLE, 'DD-MON-YY')...

Thanks for the help everyone.

(Less than 10 rep so i cant answer my own post)

Upvotes: 2

Views: 4179

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270201

Your date formats are in the wrong order. But, why can't you just do:

SELECT * 
FROM ftpritin 
WHERE start_date > :VARIABLE AND end_date < :VARIABLE

Where :VARIABLE is a date/datetime? Or, if it has to be a string:

SELECT * 
FROM ftpritin 
WHERE start_date > to_date(:VARIABLE, <right format goes here>) AND
      end_date < to_date(:VARIABLE, <right format goes here>)

Upvotes: 2

Related Questions