If you are using SQL directly to the database then you have to do all of
the translations for Selection Fields and Date (Date/Time, Date, and
Time) Fields yourself.
Selection Fields like Status are stored in the database as integers (1st
selection = 0, 2nd selection = 1, ...)
Date Fields are stored in the database as integers as well.
Date/Time fields are the number of seconds since January 1, 1970
Date fields are the number of days since January 1, 4713 B.C.
Time fields are the number of seconds since midnight
Also since you will be doing this every 2 minutes here is a performance
tip. Do all of the conversions on your side of the qualifications.
(Instead of doing the conversion on a field in the database to convert
it to what you are looking for, convert what you are looking for into
the way it is stored in the databse. If you convert the field from the
database then every row in the database will have to be checked to see
if it matched your qualifications.) i.e. Instead of converting the date
field in the database to a real date so you can check if it is < a
value, convert your value into an integer as it would be stored in the
database.
Fred________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Tuesday, December 18, 2007 3:21 PM To: [email protected] Subject: Remedy SQL query assistance... Please Help. ** We would like to do this in Access but are running into an issue with extra characters being recognized and fails to connect, currently running this in Excel. We are going to use the "< Closed" option due to it being the last in the list. And since this is going to be a process that is automated and updating every 2 minutes we kind of did not want to enter the actual date in the code. We were hoping to use a function similar to "$DATE$" to capture the current date, we currently have that function in a macro that spits the data to a csv like you mentioned. The only problem is that some of the macros will not have data in the early morning so that is why we are shooting for a SQL query. Remedy gives you an error when no data is found and since there is no way to not display errors like that it stops the rest of the Remedy macros from running behind it. I attached the macro that we are using. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Eli Schilling Sent: Friday, December 14, 2007 7:21 PM To: [email protected] Subject: Re: Remedy SQL query assistance... Please Help. ** Raul, are you running this query from within MS Excel? Crystal Reports? Also, If you're using an ARSystem ODBC driver the date conversion formula I gave you isn't necessary. ________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 12:35 PM To: [email protected] Subject: FW: Remedy SQL query assistance... Please Help. ** From: Zaldivar, Jack (ISD, IT) Sent: Friday, December 14, 2007 3:33 PM To: Martinez, Raul (ISD, IT) Subject: RE: Remedy SQL query assistance... Please Help. SELECT "HPD:HelpDesk"."Assigned To Group+", "HPD:HelpDesk"."Assigned To Individual+", "HPD:HelpDesk"."Assignee Login Name", "HPD:HelpDesk"."Case ID+", "HPD:HelpDesk".Source, "HPD:HelpDesk".Status, "HPD:HelpDesk"."Submitted By" FROM "HPD:HelpDesk" "HPD:HelpDesk" WHERE (dateadd(ss, (convert(int, "Create Date"))+(3600*-8), '01-01-1970 00:00:00') >= '12/13/2007' AND dateadd(ss, (convert(int, "Create Date"))+(3600*-8), '01-01-1970 00:00:00') < '12/14/2007') AND ("HPD:HelpDesk".Status<>'Resolved' And "HPD:HelpDesk".Status<>'Closed') ORDER BY "HPD:HelpDesk"."Case ID+" ________________________________ ************************************************************************ * This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. ************************************************************************ * __20060125_______________________This posting was submitted with HTML in it___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
<<image001.jpg>>
