How to query for date ranges such as "last week" or "last month" ?

[BenAvdicevic]

Hi,

I am trying to write some queries that return objects modified or created  in specific time frames. For example:

• yesterday
• past 7 days
• past 30 days
• etc..

Right now, I only know how to query for objects created/modified today  using the "&CurrentDate" parameter.

For example, here is a query that returns all diagrams modified today

Select Diagram WHERE [Modification Date] >= "&CurrentDate"

Now, what I would like is to have a query that returns diagrams modified in "past 7 days" or "past 14" days etc..

Is there any special keywords for this in HOPEX, or are any calculations allowed and if so - how?

[BenAvdicevic]

@PBessodes Thank you!! this works now. I can now enter a date as query parameter.

[PBessodes]

Hello,

This query works...as soon as you have activated the "Display repository administration properties..." option :

Patrick Bessodes

[BenAvdicevic]

A quick followup @PBessodes @CLAVALLEE 

Do you know if we can use Date as a Request Parameter.

I envision something like this

SELECT Application WHERE [Modification Date] >= &"Date"

[BenAvdicevic]

@PBessodes @CLAVALLEE  Thank you both!
Good to know that macros is the way to go about this.

[PBessodes · Accepted solution]

Hi,

ERQL doesn't offer this kind of capability today. You can only use system variable : &CurrentDate

But you can implement a query by a macro.

Regards

[CLAVALLEE]

Hello,

As far as I know, you would have to create query with a macro implementation.
For example, for the implementation : 

Sub FillSelectionCollection(mgobjObject as MegaObject, vntSelectorID as Variant, mgcolCollection as MegaCollection)
  Dim mgRoot, col
  mgRoot = mgobjObject.getroot
  col = mgRoot.getSelection("Select [Diagram] where [Modification Date] > '" & CDate(Now-7) & "'")
  mgcolCollection.insert col
End Sub

Hope this can help.

[BenAvdicevic]

@PBessodes Are you able to offer any help on this question?

I am writing some queries - and would really like to be able to have a way to return results "from last week" or "last month" or something like that.