Hi! I am assuming that effective_date is char data type and format yyyymmdd Try this: where substring(effective_date,1,6) < convert(char(6), getdate(),112)
Hope that works for you. CT Loo Freelance Software Developer -----Original Message----- From: Carol R [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 15, 2005 21:20 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:2218 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
