difference between date

[Jacques]

I would like to add in a list a new field which would be the difference between 2 dates.
How may I do it ?
(in Dataflex : date2 - date1)

Do I need a function typical of the database used (SQL function) or a function typical of the Dynamic AI ?

Jacques

[Carsten Sørensen]

You need a +Column with a SQL function. DATEDIFF or something...
best regards,
Carsten

[Jacques]

DATEDIFF works in "Dynamic SQL view" but not in the "Extra column and key-figure calculations" in the list reporting. For me I need a typical Dynamic AI function because if I use the report on different database, it won't be always good like it was told in the documentation. (In the documentation it was told that if you change your database (migration for exemple) , the report has not to be changed.)

[Carsten Sørensen]

Well - you can do the DATEDIFF in a Dynamic AI SQL View - then you don't have to change the report - or you can provide the dates as numeric integers in your view from the database - that will be more compatible when changing database type.

In the released version of Dynamic AI there is no build-in datediff function. In this case you can claim that the documentation is stating something that is wrong.

Best regards,
Carsten

[Bob Cergol]

Jacques,

There's no difference using any valid SQL syntax in the Select statement in a Dynamic AI view versus using it in a +column in the report. For example I use the following in an AR aging report as a +Column called "Current":
Case when DATEDIFF(Day, DocDate, GetDate()) < 30 then d_Inv_Bal_d else 0 end

You will find that for reports built on Dyn-Joins, if you reference a column in a + column formula, then you must also use that column in the details or totals, otherwise the report does not "know" that the column exists -- because only used columns are included in the select statement sent to the server -- and you will get an error when running the report.

I think its reasonable to expect that if you stick with "least common denominator" SQL syntax in your reports then they can be applied on top of different back-ends. Beyond that the different vendors invented their unique variations on SQL syntax and if you use those, Dynamic AI isn't going to translate those for you.

Regards,
Bob

[Carsten Sørensen]

Dynamic AI actually does a little bit of translation (concatenation characters e.g. between Oracle and MS SQL) - but apart from that only "build-in" functions of Dynamic AI are translated.

Regarding the inclusion of columns from a Dynamic AI Join in details or totals - what Bob is explaining is not the full explenation. If you want to always have columns available for reference in formulas (for reports based on Dynamic AI Join builder data-sources) - use INNER joins instead of LEFT joins.

Best regards,
Carsten