Re: Between dates query

A copy of this was sent to "Mike Fellows" <mfello_at_globalnet.co.uk> (if that email address didn't require changing) On Wed, 8 Sep 1999 18:33:28 +0100, you wrote:

>Hi there
>
>I have bben trying to create an SQL script used within a form to search for a value between two dates
>along the lines of ....
>
>SELECT MONTH FROM TIME T
>WHERE :BLOCK1.VAL BETWEEN T.DATE_1 AND T.DATE_2
>
>This works fine, but when I try to carry out a TO_DATE function to ensure Y2K compliancy, I get an error.
>The script is as follows
>
>SELECT MONTH FROM TIME T
>WHERE :BLOCK1.VAL BETWEEN
>TO_DATE('T.DATE_1','DD-MON-RR') AND TO_DATE('T.DATE_2','DD_MON_RR)
>

1. lose the quotes around t.date_1 and t.date_2. they are columns and should not be quoted.
2. if t.date_1 and t.date_2 are DATES -- its not needed to to_date them. If they aren't -- they should be.
3. if :block1.val is a DATE you don't need any to_dates. If :block1.val is a varchar2 - then you do but you need it around the :block1.val NOT the date columns in the database. the query would be:

  where to_date( :block1.val, 'dd-mon-rr' ) between t.date_1 and t.date_2

4) RR does not give you Y2K compliancy. It changes the way a YY type of date is interpreted -- It moves a window. The only way to ensure compliancy is to use a 4 digit year everywhere.

>Any ideas are greatly appreciated..
>TIA
>
>Mike

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 08 1999 - 13:10:04 CDT