Hello Thomas, finally I suggest
ABS((MONTH(TIMESTAMP) - MONTH(Au_RechDatum)) + (YEAR(TIMESTAMP) - YEAR(Au_RechDatum) ) * 12) for a number of months even extending a year. -----Original Message----- From: Thomas Stegbauer [mailto:[EMAIL PROTECTED] Sent: Dienstag, 20. Juni 2006 16:16 To: Thomas Stegbauer; MaxDB Subject: Re: addDate could not add monthes hi alexander, hi elke, the statement got modified like this: select Au_Id,K_Nr,Au_R_Id,Au_R_Pos from Auftrag,Kunden where Au_R_Id = K_Id and (Month(Timestamp) - Month(Au_RechDatum) + 12) mod12 >= Au_nMonate AND Au_Zyklisch = 1 AND Au_StartDatum <= TimeStamp and Au_StopDatum >= Timestampestamp with this we have the problem solved the statement must be run before 10th of a month. unfortunatly, this statement only works if au_nMonate <= 12, so it is not possible to count more than one year. to elke's suggestion ( http://lists.mysql.com/maxdb/27637 ), using oracle mode i don't think this could be the best solution, cause there are surly many side-effects. greetings thomas Thomas Stegbauer schrieb: > hi alexander, > > this could be the solution, cause currently the selections is running in > the other direction. > > select Au_Id,K_Nr,Au_R_Id,Au_R_Pos from Auftrag,Kunden > where > Au_R_Id = K_Id and > AddDate(Au_RechDatum,Au_nMonate*29) < TimeStamp AND > Au_Zyklisch = 1 AND > Au_StartDatum <= TimeStamp > and Au_StopDatum >= Timestamp > > the field auftrag.au_rechdatum is updated after the application got the > au_id whith the current_year-current_month-01 00:00:00 > > greetings > thomas > > p.s. > why doesn't you set answer-to in you outlook?, cause the answer would > run to your mailbox instead of the mailinglist? > > > > Schroeder, Alexander schrieb: > >> Hello Thomas, >> >> when the current month is MONTH(TIMESTAMP) and the target date is X, the >> number of >> months between now and X would be >> >> ( MONTH(X) - MONTH(TIMESTAMP) + 12 ) MOD 12 >> >> or did I understood your question wrong and you wanted a different >> computation? >> >> Regards >> Alexander Schröder >> SAP DB, SAP Labs Berlin >> >> >> -----Original Message----- >> From: Thomas Stegbauer [mailto:[EMAIL PROTECTED] >> Sent: Dienstag, 20. Juni 2006 14:52 >> To: MaxDB >> Subject: addDate could not add monthes >> >> hi ml, >> >> currently we are trying to port a application from a sybase sql to maxdb >> (currently 7.5.00.24-3 as it is shipped with debian 3.1). >> >> with sybase there is it possible to count nr of month's to a date, and >> the database takes care if it has 28, 30, 31 or even 29 day's. i was >> unable to find an appropriate query with maxdb, cause adddate on maxdb >> can only add day's. >> >> so the workaround was to multiply the month with 29 and check if it >> bigger than timestamp. >> >> this solution works only for sure, if the select is run at leasth before >> the 10th day of a month. >> >> does there exist better ideas with maxdb? >> >> greetings >> thomas >> >> >> >> >> > > > -- # Thomas Stegbauer # https://keyserver1.pgp.com/vkd/DownloadKey.event?keyid=0x9A3F1866FC68E91D # Key fingerprint = 5A2D FEDC 8A50 F1BB 25FB 967B 9A3F 1866 FC68 E91D -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
