This is a public Forum  publicRSS

Topic

    PRAKASH GEORGE
    How to use sysdate() in WHERE clause of ROQL query?Answered
    Topic posted February 11, 2016 by PRAKASH GEORGERegular 
    631 Views, 7 Comments
    Title:
    How to use sysdate() in WHERE clause of ROQL query?
    Content:

    Hi,

    We are trying to query incident records created in last n minutes, n hours, n days, n months etc. 

    This expression is not working and returning error.

    SELECT * from Incident where CreatedTime < sysdate() - 1

    We are using the above query to find those records created in last 1 day.

    We also tried the date_diff function but it appears  we can't use it in the 'Where' clause. 

    SELECT * FROM Incident where date_diff(CreatedTime, sysdate()) < 1800

    We use the above query to find out records created in last 30 minutes. But this throws an error - 'the WHERE clause has complex expressions involving columns in calculations or function calls'

    We need help on how to use the date_diff() or sysdate() functions.

    Thanks

    Prakash

    Best Answer

    Bastiaan van der Kooij

    hey Prakash,

    you can use it like;

    SELECT * FROM Incident WHERE CreatedTime > date_add(sysdate(), -3, 'month', 1 )

    Bastiaan

    Answer

     

    • Barrilito

      Use time() instead of sysdate().

      Regards.

    • PRAKASH GEORGE

      Hi thanks for your response.

      The time() function do not work in ROQL  (Object explorer)

      I need the function to work in the where clause in the ROQL query object explorer.

      Thanks

    • Bastiaan van der Kooij

      hey Prakash,

      you can use it like;

      SELECT * FROM Incident WHERE CreatedTime > date_add(sysdate(), -3, 'month', 1 )

      Bastiaan

    • PRAKASH GEORGE

      Thanks Bastiaan.. It worked like a charm...

      Kind regards,

      PG

    • Barrilito

      Hi PG,

      Please set Bastiaan's answer to "best answer" to show appreciation for the help and otherwise to show people the answer is given.

      Thanks.

      Regards.

    • chidambaram CT

      if face issue this "WHERE clause has complex expressions involving columns in calculations or function calls" for running ROQL

      select count(*) from Contact where date_diff(UpdatedTime, Contact.CustomFields.CO.LastUpdatedTime) > 1 

      (Where LastUpdatedTime is custom attribute.)

      How to resolve this issue?

    • Danette Beal

      hi Chidamabram,

      This post is almost a year old now and has a best answer marked. Since we want to be able to mark a best answer for each post and every situation is different, I would encourage you to start a new thread with the specific use case and versions you are working on. This will allow our Community to look at your requirements with a fresh set of eyes and if you find one hits the mark, you can choose that as a best answer. 

      If you have questions around how to create a thread, please feel free to contact me directly, or reference the following links:

      Community Guidelines

      How to Ask A Good Question]

      Regards,

      Danette, Community Manager