Current Date in query/view range (SQL GetDate() equivalent )

(0) Share

Report

Posted on by 147

I'm writing an AOT query (to be used in a view) wherein I need to set a range based on the current system date.

SQL Equivalent code -

SalesLine.ShippingDateRequested = GETDATE()

I have tried to use (CurrentDate()) but when i check the SQL code for the view, it gets translated to the literal date when the view was synchronized.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (1)

Abhishek_mishra 405 on at

Like (0)

Report

You can use today() or SystemDateget() methods.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,801 Most Valuable Professional on at

Like (1)

Report

In normal queries, you can use functions defined in SysQueryRangeUtil class, such as setting a query range value to (currentDate()).

But it wouldn't help you with a view. A view is basically a piece of T-SQL code and can't call X++ methods.

I suggest you don't try to filter inside the view. Filter the results of the view, possibly with (currentDate()).

Was this reply helpful? Yes No
147 35 on at

Like (1)

Report

Both today() and SystemDateGet() result in "Query extended range failure: Invalid function name specified at %1."

Was this reply helpful? Yes No
147 35 on at

Like (0)

Report

Hi Martin...

Yeah I know...

So there is no way to create an AOT view with the GetDate() range (when synced).

Found an easier workaround - I added GetDate() as a computed field to another view used in the query. Now I just use this new field in the range.

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (1)

Report

I wanted to suggest computed columns if I fail to convince you to use the easier way in AX. Also, adding a column containing the current date isn't the same as filtering values of another field, so there would be additional work to implement your original requirement. When you will add a range anyway, why don't you simply filter by (currentDate()), without having to develop any computed columns?

When you need it inside a view, we're not talking about "an equivalent of SQL GetDate" anymore - we're talking about SQL GetDate itself. That's exactly what your computed column does.

Was this reply helpful? Yes No
Abhishek_mishra 405 on at

Like (0)

Report

Using today() in query range value worked for me. Its filtering the data as per current date in view.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

There is a class "SysQueryRangeUtil" and method currentDate().

Was this reply helpful? Yes No
147 35 on at

Like (0)

Report

Background info:

The structure is like this:

The final view (SalesLineCosts_View) is for determining the costs of sales lines delayed in shipment. The requirement also needs to convert the sales prices to the accounting currency (CurrXR_View).

The currentDate comparison is required in two places - one is simply to filter the sales lines for delayed shipment.
The other is used to find the correct exchange rate for sales price conversion (given exchange rates are re-evaluated often enough)

For the first comparison, you are right is can quite easily be done in X++ code.

For the second, the cross join between SalesLine and CurrXR can become needlessly huge, bogging down the performance of the entire query (if not filtered at query/view stage itself).

Filtering this via X++ leads the report to take ~ 4 min (240 sec) to run.

Adding the Computed Column for getdate() and putting a range when joining with CurrXR_View : ~ 10 sec.

----------

To be honest, I didn't want to add an extra column. which is why i asked if there was any way to create the query/view in a way so that GetDate() will appear in the SQL definition of the view....

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (1)

Report

Of course you couldn't expect us to take into account requirements you didn't mention. Nevertheless I still don't see why you can't filter ToDate with the current date sent from AX.

My suggestion, using (currentDate()), does add a range to the query to database. If you're fetching all data and filtering them in the application layer, you're doing something completely different then I told you (and I agree that it's a complete waste of resources).

Computed columns are very powerful and I personally use them a lot, but they they're not necessary for simple filtering.

Was this reply helpful? Yes No