Oracle SQL - Variable between two dates

Question

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)

Gordon Linoff · Accepted Answer · 2013-09-06 16:34:11Z

2

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>)

answered Sep 6, 2013 at 16:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Cade W. Over a year ago

Im not a pro at databases, but to_date() wouldn't work on these fields, thats why i used to_char().

Dan Bracuk Over a year ago

If the field is already a date, using to_date() is unnecessary. However, if the variable is a string, using to_date() on it seems like a good idea.

Cade W. Over a year ago

Is this not backwards? I want my variable to be between start_date and end_date. start_date will always be earlier on the calendar than end_date, so wouldn't it be start_date < to_date(var) AND end_date > to_date(var) ?

Cade W. Over a year ago

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)

Gordon Linoff Over a year ago

@CadeWard . . . The logic for the condition was taken from your question.

Collectives™ on Stack Overflow

Oracle SQL - Variable between two dates

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related