I finally realised what's the problem with this query!! Took me ages to figure it out but it was staring me right in the face.
I used the current year as the year part of the calculation. But one of the birthdays is 29 February. And 2005 is not a leap year. So when it calculated the day/month values as a date as day/month/2005 the 29 February caused an error. All I had to do was change the calculations from using the current year to using a leap year and the function worked again. Der!! Cheers Mike Kear Windsor, NSW, Australia Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com Business Strength ColdFusion,PHP,ASP,ASP.NET hosting from $15/Month -----Original Message----- From: Michael Kear [mailto:[EMAIL PROTECTED] Sent: Sunday, 6 March 2005 3:15 PM To: SQL Subject: What's wrong with this query? I've been using this query for ages, until the end of last year when it started throwing errors. I got around it by hard-coding some of the parts of it, and now I'm getting back to trying to fix it. Can anyone see what's wrong with it? SELECT annivID,Event,Day,Month,Year,name,comments FROM anniversaries WHERE DATEPART(wk,cast(rtrim(cast(Month as char(2)))+'/'+rtrim(cast(Day as char(2)))+'/'+cast('2005' as char(4)) as datetime))= DATEPART(wk,getdate()) order by month, day The error it gives is: Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. What it's supposed to do is produce a list of anniversaries where the month and date are between Sunday of this week and Sunday of next week, without regard to the year. It's a list of celebrity birthdays. Cheers Mike Kear Windsor, NSW, Australia Webmaster, Bluegrass Australia http://bluegrass.org.au ----------------------------------------------------- Not a preacher, not an expert but a fan - speaking from the heart. Talking dog on http://Bluegrasscountry.org ----------------------------------------------------- We are a Bluegrass Unlimited Reporting Program ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Protect your mail server with built in anti-virus protection. It's not only good for you, it's good for everybody. http://www.houseoffusion.com/banners/view.cfm?bannerid=39 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2206 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=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
