none
Microsoft Query - ODBC Remedy DB

    Question

  • Hi ALL ,

    Am using Microsoft Query ODBC  to connect to the BMC Remedy from excel . Now am stuck in a situation i wish to automate it

    but for that i need to provide a function that works like getdate() in sql server or sysdate in oracle .

    Please let me know what Date function should be passed in the where clause to get the current date data . The Remedy DB can be either Oracle or SQL Server one am not sure .Am asking the question here as am using Microsoft query to connect to the Remedy DB .

    Please Experts help very near to Automating a project stuck just because am not able to pass the current date in the query :( .

    SELECT "HPD:Help Desk"."Assigned Group", "HPD:Help Desk"."Assigned Support Company",
    "HPD:Help Desk"."Last Modified Date"
    FROM "HPD:Help Desk" "HPD:Help Desk"
    WHERE ("HPD:Help Desk"."Last Modified Date" > {ts '2014-02-02 00:00:00'}) .

    This Works but i don't want to provide dates manually all the time just provide the Current Date function .

    Please help

    Thanks

    Priya

    Saturday, February 15, 2014 9:28 PM

Answers

  • As I said, I am able to vouch for what works with your ODBC Driver. I did some googling, and I found a few related problems, including
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=150522
    which looks very similar to your problem. Unfortunately, there is no answer.

    It appears that you are using AR System ODBC Driver of which I'm entirely unfamiliar with.

    The alternative would be to build the query string and inject either the date, or if you can determine whether you are talking to SQL Server or Oracle and inject the correct function.

    Or does Oracle have a date data type? In such case this should work on both:

       cast(CURRENT_TIMESTAMP AS date)

    As long as the ODBC driver does not get in the way, that is.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, February 16, 2014 5:51 PM

All replies

  • This may work:

    {fn concat({fn current_date()}, {fn concat('T', {fn current_time()})})}

    This is uses only ODBC functions to construct the string, so it should not matter whether you are running against SQL Server or Oracle. But I can't give any warranties for the latter.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 15, 2014 9:56 PM
  • Hi ,

    Thanks a ton for the reply can you please insert the above code in the SQL script i have posted as it will help me to avoid errors .

    Thanks

    Priya

    Saturday, February 15, 2014 10:01 PM
  • That should be in place of the {ts} thingy you have now. If you try

    SELECT {fn concat({fn current_date()}, {fn concat(' ', {fn current_time()})})}

    you will see what it returns.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 15, 2014 10:47 PM
  • Hi ,

    I  might be doing some mistakes i think as it says lexical not found .

    Can you please edit by actual query and replace it with the date function you have mentioned that can help me to avoid mistakes am doing .

    Thanks

    Priya

    Sunday, February 16, 2014 8:16 AM
  • The query would be

    SELECT "HPD:Help Desk"."Assigned Group", "HPD:Help Desk"."Assigned Support Company",
    "HPD:Help Desk"."Last Modified Date"
    FROM "HPD:Help Desk" "HPD:Help Desk"
    WHERE ("HPD:Help Desk"."Last Modified Date" > {fn current_date()}

    You may note that this is a lot shorter than what I posted last night. I overlooked that you only cared about the date. (Which I should have realised from the start, since query would not make sense if you also included the current time.)

    I like to stress that I can vouch for that this actually works. These fn things are part of the ODBC standard, but that does not necessarily mean that both ODBC drivers implement then.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, February 16, 2014 1:04 PM
  • Hi ,

    Thank you so much for your assistance .

    I replaced my previous sql script with this ("HPD:Help Desk"."Last Modified Date" = {fn current_date()})
    but now am getting a lexical error 3700 . I have searched the entire net but no luck even the Remedy User group i didn't got the solution . My entire Automation is on halt because of the current date function .

    Is still something i can try please do assist .

    Thanks again

    Priya

    Sunday, February 16, 2014 2:17 PM
  • As I said, I am able to vouch for what works with your ODBC Driver. I did some googling, and I found a few related problems, including
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=150522
    which looks very similar to your problem. Unfortunately, there is no answer.

    It appears that you are using AR System ODBC Driver of which I'm entirely unfamiliar with.

    The alternative would be to build the query string and inject either the date, or if you can determine whether you are talking to SQL Server or Oracle and inject the correct function.

    Or does Oracle have a date data type? In such case this should work on both:

       cast(CURRENT_TIMESTAMP AS date)

    As long as the ODBC driver does not get in the way, that is.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, February 16, 2014 5:51 PM
  • Hi ,

    Thank you so much for your time and efforts but none of the function is supported . i need to find some other way now .

    Thanks again& Take care

    Priya

    Monday, February 17, 2014 2:24 PM