Re: Direct SQL - How can I use a $TIMESTAMP$ reference? - Solved

[Leigh Gruber]

** Everyone,   I think that Thomas’ method would work just fine, but I received a solution off line that is very clean and seems to work perfectly without the extra field.   Mark W. sent me the following solution.  I don’t know the etiquette of the list when someone sends a solution off-line, but the results are just as I expected.   SELECT count(*) FROM Issue WHERE Customer_Abbreviation = '$Customer_Abbreviation$' AND Create_Date > (DATEDIFF(s,'1/1/1970','$TIMESTAMP$') - ($Threshold Days$ * 24 * 60 * 60))   Many thanks, Leigh From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Thomas Bean Sent: Friday, October 27, 2006 5:25 PM To: arslist@ARSLIST.ORG Subject: Re: Direct SQL - How can I use a $TIMESTAMP$ reference?   Hello Leigh, Try setting the $TIMESTAMP$ value to hidden, display-only integer field, then use the integer field name reference in place of the $TIMESTAMP$ keyword reference in your SQL statement.   --Thomas   ----- Original Message ----- From: Leigh Gruber Newsgroups: gmane.comp.crm.arsystem.general Sent: Friday, October 27, 2006 14:55 Subject: Direct SQL - How can I use a $TIMESTAMP$ reference?   ** Hi Everyone,   I met a number of you at the 2005 RUG and am sorry that I didn’t get to this year’s gathering.  My husband and friends DID have successful English Channel relay swim over and back so I missed the event for a good reason. Hope to see you next year!   Now my question…. I am writing new workflow and have a direct SQL statement in an escalation which will execute occasionally.  Unfortunately I get an error due to the conversion of the $TIMESTAMP$ value into a nicely formatted date/time stamp.    How can I get the current time to come out in epoch time so it will “play” nicely with the create date and the calculated offset?   This is the direct SQL statement. SELECT count(*) FROM Issue WHERE  Customer_Abbreviation = '$Customer_Abbreviation$' AND Create_Date > ($TIMESTAMP$ - ($Threshold Days$ * 24 * 60 * 60)) And this is what I see when the SQL log is turned on for the server. SELECT count(*) FROM Issue WHERE  Customer_Abbreviation = 'zSENTEL' AND Create_Date > (10/27/2006 3:33:51 PM - (28 * 24 * 60 * 60)) ----------------------------------------------------^ (*** ERROR *** Line 1: Incorrect syntax near '3'. (SQL Server 170))   We’re running SQL Server 2000 on Windows 2003 OS.   Many thanks, Leigh   The information contained in this message may be privileged and/or confidential. If you are not the intended recipient, or responsible for delivering this message to the intended recipient, any review, forwarding, dissemination, distribution or copying of this communication or any attachment(s) is strictly prohibited. If you have received this message in error, please so notify the sender immediately, and delete it and all attachments from your computer and network.   __20060125_______________________This posting was submitted with HTML in it___ __20060125_______________________This posting was submitted with HTML in it___ __20060125_______________________This posting was submitted with HTML in it___