ASP Date Syntax Using Remedy Driver Connection 1

[Rockorama]

I am trying to write an ASP script which connects to our Remedy server with the ODBC driver provided in the client install. In my Recordset's SQL filter, I AM able to successfully query any text fields with the same field names and values as I use in the Advanced Search bar of the User Tool, such as Request ID.

However, I am unable to query any of the Date fields in our schema using this same syntax. I've tried pasting in the Date from a ticket I queried using a specific Request ID, I've tried putting a valid Date/Time value in a variable and using the variable in my WHERE clause, and I've even tried using the "1/1/1970-in-seconds" value instead of one recognized by the User Tool.

In all these cases, my results set looks like I've just typed garbage fieldnames/values in my script. (And I've even tried THAT--same result.) No records are returned.

Using the Remedy driver in my DSN should allow this to behave the same as the User Tool, but it doesn't.

Has anyone ran into this before?

 

[Rockorama]

I found the answer, if anyone else runs into this.

First, I have a function that takes a time arguement and formats the time as the following: {ts '2002-07-01 15:00:00.000'}.

Then, I have a date variable defined by user input, which I also cast as a date (CDate()).

Then, I pass this date variable to the function, like Function(dateVar).

Then, I define a string variable with Function(dateVariable) as the date comparison in my WHERE clause. An example would be:
whereString = chr(34) & &quot;Create_Date&quot; & chr(34) & &quot; < &quot; & Function(dateVar)

Finally, I write my SQL query, and for the WHERE clause I write:
SQL = SQL & &quot;WHERE ('whereString') &quot;

Easy as pie! When you know it, that is....

 

[johnzy]

Hi Rockorama,

Can you kindly send me a copy of teh script.

I desperately need that as I am in a similar problem with dates in remedy.

Regards,
John C
john.chandra@weyerhaeuser.com