A quick follow up on this - the problem with the designer seems to be that it thinks datediff returns a type of DATE or DATETIME not integer. So when you add the criteria as <= 30 it generates an error of 'Wrong data type'.
Yes, I will go and add this to IZ. On 10/2/06, Andrew Jensen <[EMAIL PROTECTED]> wrote:
True the use of the view with the sliding window added some complication - but if all you want is the records with some date in a column that falls within the next 30 days from today. SELECT ...... where datediff( 'dd', CURRENT_DATE, "aDateColumn" ) <= 30 AND "aDateColumn" > CURRENT_DATE That will do it. NOTE the order of the paramters to datediff are significant here it must be datediff( 'dd', lower-date, higher_date ) else you receive a negative number and all future dates are returned. Still the problem exists that this query cannot be constucted ( at least I have not been able to ) in the query designer view. You must create it in the SQL view of the query designer, which means also, that to save it you must first turn escape processing off. It runs however, just fine with escape processing turned on. If someone can show me a way to do this in the designer I would be very grateful - but with 2.0.4 RC3 it will not accept this. On 10/2/06, David Ashmore <[EMAIL PROTECTED]> wrote: > > Andrew Jensen wrote: > > You want to use the datediff function and the check this integer. > > > > There is a rather detailed example of doing this, as it might be > > used for a > > call back routine in a contact managment setting at - along with an > > example > > Base database. > > > > http://www.oooforum.org/forum/viewtopic.phtml?t=24480 > > > > That should point you in the right direction. If you have further > > questions > > don't hesitate to ask here on at the forum. > > > > HTH > > > > Drew Jensen > > > When I edit the query the wizard creates is there a way to modify the > query. > <= CURDATE( ) + 30 > the <= curdate works but of course only for today and all previous > entries. > How can it be corrected or add 30 days to the curdate()? > Is is just syntax I'm missing? <= CURDATE( ) + 30 > > -- > Regards > Dave Ashmore > SOHO IT Solutions llc > 1934 S. Delaware Dr. > Easton, PA 18042 > 610-258-7128 > http://callsohoit.com > Sometimes I can be reached via Skype as djash34 > Disclaimer: The below quote is randomly generated and I may or > may not agree with the quote. > ################################################################ > (MS-FORTUNES) > % > In most countries selling harmful things like drugs is punishable. > Then howcome people can sell Microsoft software and go unpunished? > ################################################################ > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > >
