get order with created time greater than 2 hours

[Former Member]

Hi Experts,

My requirement is to fetch all orders ,whose created time is greater than 2 hours. I am writing flexible query like :

SELECT {o.pk} FROM {Order as o} WHERE TIMESTAMPDIFF(hour,'currentsystime',{o.creationtime}) > 2 ;

But this is not working somehow.

Can anyone help on this.

Thanks in advance Sid

[Former Member · Accepted solution]

Hi siddharth,

Please try below query:

 SELECT  {o.pk}  FROM {Order as o} WHERE {o.creationtime} > NOW() - INTERVAL 2 HOUR

If it doesn't work please tell me which database are you using.

Best Regards,

[Former Member · Accepted solution]

Hi Jakub,

This worked . Have to use '<' instead '>' . Thanks.

[Former Member]

Hi

What is the error you are getting?

I guess it's the issue with INTERVAL as NOW() can be used on hana (according to documentation). You could try using SECONDS_BETWEEN(NOW(), {o.creationtime}) < 3600*2 or something of that sort. Sorry I can't test it myself. Please see this article for more datetime functions.

Hope this helps,

[rahulverma94]

Hi ,

This query did not work in SAP HANA DB. Could you please update the query for HANA db? We are using HANA on all the servers.

Regards, Rahul