Re: Sql problem

Stefan.Richter_at_Materna.DE (Stefan Richter) writes:

>In article <3qiffb$e9f_at_hasle.oslonett.no>, steinas_at_ifi.uio.no says...
 

>#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.
 

>#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.
 

>#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?
 

>#Thanks in advance.
 

>#Steinar

>ORACLE provides a (not very comfortable) way, using to_char:
 

>SELECT DISTINCT studreg.studno FROM studreg
> WHERE to_char(birthday,'dd') BETWEEN 1 AND 15
> AND to_char(birthday,'mm') BETWEEN 1 AND 5;
 

>It would be interesting for me, also, if there is a easier way ...
 

>Stefan

The above solution may not work correctly because of implicit datatype conversions. Where clause should read:
WHERE to_char(birthday,'DD') between '01' and '15' AND to_char(birthday,'MM') between '01' and '05';

Alternative solution: put a to_number() around the to_char(). Cheers,

Marc. Received on Tue Jun 06 1995 - 00:00:00 CEST