Oracle and dates 1

[KenCunningham]

Good morning (or whatever it is where you are!),

I've been struggling to understand how I can select records from a table, where the records are defined by date, held in c09_date_input in date format. A basic query such as:

select c09_date_input
from t09_memos
where c09_date_input > '01-JAN-90'
and c09_date_input < '31-OCT-96'

retrieves no records, as does:

select c09_date_input
from t09_memos
where c09_date_input between '01-JAN-90' and '31-OCT-96'

I'm baffled. I've searched the forum with what I think are sensible keywords, but can't find anything that might help. It could be that my knowledge of dates and SQL is somewhat faulty, but I'd be grateful if someone could explain what might be happening (or not happening as is more likely the case). Many thanks.

 

[sem]

First of all &quot;between&quot; is equivalent to <= and >= (includes both ends) so if your table contains dates '01-JAN-90'
and '31-OCT-96' they are not retreived by the first one.

Another possible problem is date formats, but if you run both queries within the same environment (pereferably within the same session) they must return the same data. To avoid possible discrepancy you may use explicit to_date conversion.

And at last if you run these queries by different users you may select data from different tables (synonyms or own tables with the same name may exist).

 

[KenCunningham]

Many thanks. I think the problem is with the date format - could you explain the 'to_date' conversion you mention, please, as I've never used this particular facility. Thanks again.

 

[sem]

The syntax of to_date is
to_date(<string>[, <format_mask>, [<nls_lang>]])

In your case you should use:

09_date_input > to_date('01-JAN-90','DD-MON-RR', 'nls_date_language=english'))
and c09_date_input < to_date('31-OCT-96','DD-MON-RR', 'nls_date_language=english'))

You may ommit the third parameter if using English only.
You may also alter session to use 'dd-mon-rr' format mask as a default:
alter session set nls_date_format='dd-mon-rr';

In this case you may rely on Oracle and do not use explicit conversion (return to your initial variant).

 

[KenCunningham]

Many thanks Sem, I have used the alter session option and this is working fine now. Thanks again!

 

[sem]

Pleas be careful: alter session command issues commit, you can not safely use it within transaction.