TMSUSERS Archives

The Museum System (TMS) Users

TMSUSERS@SI-LISTSERV.SI.EDU

Options: Use Monospaced Font
Show Text Part by Default
Show All Mail Headers

Message: [<< First] [< Prev] [Next >] [Last >>]
Topic: [<< First] [< Prev] [Next >] [Last >>]
Author: [<< First] [< Prev] [Next >] [Last >>]

Print Reply
Subject:
From:
"Hansen, Stephanie" <[log in to unmask]>
Reply To:
The Museum System (TMS) Users
Date:
Thu, 31 Mar 2011 13:12:17 -0500
Content-Type:
text/plain
Parts/Attachments:
text/plain (1 lines)
Perfect! Thanks!!! 


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

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.

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.

ATOM RSS1 RSS2