Re: Sql problem

steinas <steinas_at_ifi.uio.no> wrote:
>Hi!
>I,m trying to assemble a sql sentence selecting students who have birth-
>day between day x and day y and month between month x and month y independent
>of year.

This is a very strange request.

>In vb this sentence works fine :
>SELECT DISTINCT studreg.studno FROM studreg WHERE day(birthday)
>BETWEEN 1 AND 15 AND month(birthday) BETWEEN 1 AND 5.

Note that the DAY and MONTH functions are (I believe) not ANSI-standard SQL. So don't expect other vendors to use the same command names.

>Can't find an easy way to translate this to Oracle.
>What do I have to do to get this sentence to work in Oracle?

...WHERE to_char ( birthday , 'DD' ) between 1 and 15

     AND to_char ( birthday , 'MM' ) between 1 and 5

Check out the wonders of TO_CHAR and TO_DATE and the date format mask elements, some of which are quite clever, IMHO.

For example, 'MM-DD-YYYY' would be '05-31-1995', while 'DAY DD-Mon-YY' would be 'WEDNESDAY 31-May-95'.

Notice that the format mask is case sensitive -- DAY maps to WEDNESDAY but Day maps to Wednesday.

Cheers.  

 -Tom

-- 
Thomas B. Cox   tcox_at_netcom.com
Author of _Oracle Workgroup Server Handbook_, ISBN 0-07-881186-4
                             http://www.oracle.com/info/magazine/orapress.html
...be seeing you at IOUW '95!