I asked GS for the alerts they showed us in the webinar.  I don't think I made any changes to their SQL...

Select Loans.LoanNumber, Constituents.DisplayName As 'Lender/Borrower', Loans.EndISODate as EndDate 
from Loans inner join vConXrefs_Classic on Loans.PrimaryConXrefID=vconxrefs_classic.ConXrefID 
inner join Constituents on vConXrefs_Classic.ConstituentID=Constituents.ConstituentID 
where  len(Loans.EndISOdate) = 10 /*if len is 10, date has year and month and day*/ and datediff(week,getdate(),convert(datetime,loans.EndISODate,121)) between 0 and 8
Order By Loans.EndISODate

...and it worked like a charm for me!

--Jessica


_________________________________
Jessica Milby 
Collections Information Project Manager 
Philadelphia Museum of Art 
Phone: 215-684-7283 
Fax: 215-235-0035 
[log in to unmask]

-----Original Message-----
From: The Museum System (TMS) Users [mailto:[log in to unmask]] On Behalf Of Stephanie Hansen
Sent: Thursday, March 31, 2011 1:57 PM
To: [log in to unmask]
Subject: SQL Help

Hello,
I'm trying to create an alert based on the Loans.EndISODate. I'm having a hard time finding the magic SQL words to convert that field to a date. I keep getting the error "Conversion failed when converting datetime from character string." If anyone knows the sql, I would be so greatful!!
Thanks!
Stephanie Hansen
Milwaukee Art Museum

To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:

     signoff TMSUSERS

     //  eoj


You will receive a confirmation that your subscription has been removed.

To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:

     signoff TMSUSERS

     //  eoj


You will receive a confirmation that your subscription has been removed.