Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL date range question
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
![]() |
![]() |