Re: SELECT BETWEEN Dates

If you do to_date('11-aug-1999','dd-mon-yyyy') then the resultant value is: 11-aug-1999 00:00:00.The only way that this would get any rows is if all of the values in REV_DATE were stored with the time portion trunc()d off. By having the second date as 12-Aug, you are specifying all times from 11-Aug-1999 00:00:00 to 12-Aug-1999 00:00:00.

This has the potential for some unexpected repercussions because it will give you any records on the 12th that have the time truncated. In most of the applications I have worked with this is not acceptable behavior so I would end up writing a WHERE clause like:

where rev_date >= to_char('19990811','YYYYMMDD')

     and rev_date < to_char('19990812','YYYYMMDD');

That way I am assured that I will only get records that have the Aug 11 date.

Ken

Eamonn Keating wrote:

> On the oracle database the following statement will not retrieve any rows
> SELECT PartCode,SPEC_REV_NO, to_char(CHANGE_DATE_TIME,'dd/Mon/yyyy
> hh24:mi:ss'), to_char(REV_DATE,'dd/Mon/yyyy'),Userid,Comments FROM SPECAUD
> WHERE REV_DATE BETWEEN to_date( '11-Aug-1999' , 'dd-mon-yyyy') AND
> o_date( '11-Aug-1999' , 'dd-mon-yyyy')
>
> wheras the following will
>
> SELECT PartCode,SPEC_REV_NO, to_char(CHANGE_DATE_TIME,'dd/Mon/yyyy
> hh24:mi:ss'), to_char(REV_DATE,'dd/Mon/yyyy'),Userid,Comments FROM SPECAUD
> WHERE REV_DATE BETWEEN to_date( '11-Aug-1999' , 'dd-mon-yyyy') AND
> o_date( '12-Aug-1999' , 'dd-mon-yyyy')
>
> for data on 11-Aug-1999 ?
> any help would be appreciated
>
> Regards
> Eamonn
Received on Wed Aug 11 1999 - 09:41:39 CDT