Re: (Q) Subtract 6 months from a date: result=error -1267!!!

On 12 Apr 1994, Quentin North wrote: > Trouble is we dont have 4GL. We are trying to do this in a 'batch' > manner using dbaccess. We are trying to do a select from a table of > transactions so that we can summarise the figures on historical basis > into another table over 3/6/12 months for financial aging. BEWARE, This is not pretty, but If you have a table called test_table and it has a field named date_field defined as date the following sql seems to get you everything from September 30, 1993 on. If you change the 92 in the last line to a 91 you seem to get everything from Oct. 1 on. A truly automated 4gl solution would be nicer but since you are in sql--dbaccess-- you must already be doing some shell script editing. This example assumes a half year is 182 days. select * from from test_table where (date_field >= extend(date("03-31-1994"), year to day) - 89 units day and year(date_field) = "1994") or (date_field >= extend(date("12-31-1993"), year to day) - 92 units day and year(date_field) = "1993") I warned you it wasn't pretty! with all the caveats the bandwidth will allow -- Con Woodall Colorado St. U.; Veterinary Teaching Hospital; Ft. Collins CO 80523; 303-491-1244 FAX 303-491-4414 cwoodall@vth1.vth.colostate.edu My employers don't even know about this. --