ceiling problem - How to find which quarter

Hi, In connection with following issue, we found this problem while doing porting from SQLserver to Oracle and Informix Our requirement was to findout the Quarter( i.e. 1,2,3,4) for a given date in that year( Year start with 1st Jan) SELECT CEILING(MONTH(ip_date)/3) ...... for above stmt. if date is 1st March then select stmt. o/p is '2' instead of '1' as it should be first quarter. Currently We are using the following solution, if any other better solution is avail. then let me know, ( It should be a single SELECT stmt) SELECT DECODE(MOD(MONTH(ip_date),3),0,MONTH(ip_date)/3,FLOOR(MONTH(ip_date)/3)+1) Girish > -----Original Message----- > From: Jonathan Leffler [SMTP:jleffler@informix.com] > Sent: Tuesday, January 26, 1999 2:48 PM > To: Girish Kagrana > Cc: Informix NewsGroup > Subject: Re: What should be the output of ceiling? > > On Tue, 26 Jan 1999, Girish Kagrana wrote: > > What should be the output of ceiling? > > > > Here is some comparison on Informix(7.30 UC3)/Oracle/SQLServer > > > > Select stmt used 'SELECT CEILING (ip_value) .' > > > > Database ip_value op_value > > ----------- -------- ------------ > > Informix 10 11 > > Oracle 10 10 > > SQLServer 10 10 > > > > [...snipped...] > > > > For last set i.e. when ip_value is 10.1 o/p is same for all the > databases, > > then why is the first two result sets different in Informix, is this > Bug > > in Informix or is it the correct o/p of ceiling? > > I would say that Informix is wrong -- the output of CEILING(10) should > be 10, the smallest integer greater than or equal to the input value of > 10. You could easily get the buggy effect if you implement CEILING() > as: > > TRUNC(value + 1, 0) > > This doesn't handle the border case correctly (it gives 11 instead of > the correct 10). I don't know that's what was used, but your > observations are consistent with it. I suppose that the correct answer > could be obtained by using (some unidentified pseudo-code): > > rv = TRUNC(value, 0); > IF rv != value THEN rv = rv + 1; END IF > RETURN rv; > > Yours, > Jonathan Leffler (jleffler@informix.com) #include <wish/I/was/skiing.h> > Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN > Informix IDN for D4GL & Linux -- http://www.informix.com/idn >