If I understand the problem correctly since you are dealing with a time frame that is less than the first of the month, just don't worry about the day component.
where Year(EffectiveDate) <= Year(Now()) and Month(EffectiveDate) < Month(Now()) In your example that would get dates that are in the prior year and prior month, basically anything that occurred before the first of the current month. Ray Thompson Tau Beta Pi (www.tbp.org) The Engineering Honor Society 865-546-4578 -----Original Message----- From: Carol R [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 15, 2005 8:20 AM To: SQL Subject: Date Manipulation in SQL Hi. Using SQL (IBM DB2 vers 7). Trying to take the following where statement and automate it. Currently the user has to change the date in the query every month. Where effective_date<'2005-03-01' Next month it'll have to be changed to <'2005-04-01'. So in the exammple where effective_date <'2005-03-01', would like to have sql automatically calculate so that even if the query is executed on March 15th, the records returned would still have effective dates of ONLY up to 2/28/2005 (anything less than 3/1/2005). any ideas? Started playing with : year(effect_date)<=year(current date) and month(effect_date)<month(current date) and day(effect_date) -> but this is where I get stumped. Thanks! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2217 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
