Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL date range question

Re: SQL date range question

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: 2000/05/25
Message-ID: <lpiqiskp8ihiafdko42b24b9godcdfvdqi@4ax.com>#1/1

On Wed, 24 May 2000 17:05:43 -0700, "Dave Knight" <no.spam.daveknight_at_proaxis.com> wrote:

>WHERE
> ENTORDER."ESTABLISHED" > {d 2000-05-03'}
>
>SQLPlus does NOT like the formatting on this. I get the error message:
>ORA-01840: input value not long enough for date format

Use Oracle's built-in to_date function. For example:

SELECT blah, blah, blah
FROM entorder
WHERE
entorder.established > TO_DATE('1-Jan-2000','dd-mon-yyyy');

Be aware that dates in the database may contain a time component as well as just the date. For the above comparison it won't matter, but consider the following where you are looking for dates less than or equal to a given date:

WHERE
entorder.established <= TO_DATE('1-Jan-2000','dd-mon-yyyy');

If the established date in this case happens to have a time of day of say 8:00am (anything other than midnight), you won't get the results you are after. There are two things you can do about that. One is to use the built-in TRUNC function to eliminate the time of day from a date. For example:

WHERE TRUNC(entorder.established)

           <= TO_DATE('1-Jan-2000','dd-mon-yyyy');

The problem with TRUNC is that it precludes the use of any index on the established date. Another approach is to carefully code your comparision so that time of day does not matter. For example:

WHERE entorder.established

          < TO_DATE('1-Jan-2000','dd-mon-yyyy')+1;

This time less than (<) was used instead of less than or equal to (<=), but the date was advanced by one day. Any established date with a time anytime during 1-Jan-2000 would be picked up by this query.

Hope this helps.

Jonathan



jonathan_at_gennick.com
http://gennick.com
Brighten the Corner Where You Are Received on Thu May 25 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US