Re: analyse month in a date field

Dies ist eine mehrteilige Nachricht im MIME-Format. --------------87614AA94F180287A67C501B Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Jonathan Leffler schrieb: > On Fri, 11 Dec 1998, Dirk Emmermacher wrote: > > Jonathan Leffler schrieb: > > > Dirk Emmermacher wrote: > > > > I 've got a problem to analyse the month in date field. > > > > The month has to be analysed in a format. > > > > Is it possible??? > > > > We are working with Informix 4.00 SE. > > > > Format generator is on the system. > > > > > > I suspect you're looking for a minor variant on: > > > > > > SELECT MONTH(date_column) month_num > > > FROM SomeTable > > > WHERE MONTH(date_col2) > 9; > > > > Yes, I want to use this statement function in a format to generate > > bills. Its for an newspaper. There are people, who get an reduce price > > the first year, but it is not only january the begin of the contract. > > > > Do you any ideas to get the month in a format??? > > I don't understand your question, yet. Your English is infinitely better > than my German, but I'm still not sure what you're after. > > As far as I can understand, you have a billing system for a newspaper. > You need to be able to determine whether the customer is still entitled > to the reduced rate, based on the month in which they first ordered the > paper. If more than one year has elapsed since they subscribed, they > have to pay the full rate. The first_subscribed_date is stored in the > database in a DATE field. [Aside: I assume the billing is being done monthly > rather than annually.] I assume that if the first subscription date is > at any time during the month, then the customer gets the rest of the month > and the next 11 months at the reduced rate. From the first of the same > month a year later, the higher rate applies. This is as though the start > date for billing was always the first of the month. > > Assuming that's correct, there are several possible approaches to determining > whether the billing date is such that the subscription should be at the higher > rate. I'd probably design a stored procedure to handle it: > > CREATE PROCEDURE first_day_of_month(dt DATE) RETURNING DATE;> RETURN MDY(MONTH(dt), 1, YEAR(dt)); > END PROCEDURE; > > CREATE PROCEDURE billing_rate(subscribe_date DATE, billing_date DATE DEFAULT TODAY) > RETURNING CHAR(2);> DEFINE fd_subs DATE; > DEFINE fd_bill DATE; > > LET fd_subs = first_day_of_month(subscribe_date); > LET fd_bill = first_day_of_month(billing_date); > > -- Worry about leap years, too?!? > IF fd_bill - fd_subs >= 365 THEN > RETURN "hi"; > ELSE > RETURN "lo"; > END IF; > > END PROCEDURE; > > If I'm still way off track, please explain more precisely (but less > concisely) what you need to do. > > Yours, > Jonathan Leffler (jleffler@informix.com) #include <witticism.h> > Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN > Informix IDN for D4GL & Linux -- http://www.informix.com/idn Hi Jonathan! Yes, thats what I mean. But the only problem I have is this: At the moment I don't use 4GL because my HP-UP system is old and very slow (HP-9000 Model 807S). In summer we will change this machine to a faster one and with higher version of the Informix database I will try your solution!! Take care. Dirk Emmermacher --------------87614AA94F180287A67C501B Content-Type: text/x-vcard; charset=us-ascii; name="NDSTURNERBUND.vcf" Content-Transfer-Encoding: 7bit Content-Description: Visitenkarte f'r Dirk Emmermacher Content-Disposition: attachment; filename="NDSTURNERBUND.vcf" begin:vcard n:Emmermacher;Dirk tel;fax:+49 511 980 97-461 tel;work:+49 511 980 97-34 x-mozilla-html:FALSE url:www.ntb-infoline.de org:Nieders'chsischer Turner-Bund e.V.;EDV adr:;;Maschstr. 18;Hannover;Niedersachsen;30169;Deutschland version:2.1 email;internet:NDSTURNERBUND@t-online.de x-mozilla-cpt:;-1 fn:Dirk Emmermacher end:vcard --------------87614AA94F180287A67C501B--