Date Arithmetic in DAI ?

[Bob Cergol]

Hi,

I want to do some book-to-ship-to-bill reports that give information on "turn times". I also will eventually want to attempt scheduling reports.

In my Dataflex programming I am used to doing simple arithmetic directly on date data without worrying about conversion.

Somewhere I recall reading in the DAI documentation that either it is limited or not possible to use date fields in DAI formula fields. Of course many people have done all manner of aging reports on accounting data living in SQL databases, and no doubt also in Dynamic AI, so I know there is some way to do this.

I'm probably being lazy -- as I should check my SQL On-Line books -- but think someone here already knows the quick easy answer to this.

Thanks!
Bob

[Carsten Sørensen]

Hi,

You can do rather sophisticated date arithmetic using +Col SQL type columns. The syntax will be the one of your database. If MS SQL you would be able to use:

DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')

instead of the static dates entered above you would insert the SQL field-names of your DATE columns.

As I recall - you can also just do date2-date1 and get a number of calendar days as a result.

other options are:
SELECT DATEADD(year,2147483648, '2006-07-31');
SELECT DATEADD(year,-2147483649, '2006-07-31');


DATEPART(year, '12:10:30.123')
,DATEPART(month, '12:10:30.123')
,DATEPART(day, '12:10:30.123')
,DATEPART(dayofyear, '12:10:30.123')
,DATEPART(weekday, '12:10:30.123');

etc. etc.

Best regards,
Carsten

[Bob Cergol]

Hi Carsten,

Thanks for that information.
[Invoiced] - [Entdate] does not work,
but this does just what I need to start with...simple!
DATEDIFF(dayofyear, [Entdate], [Invoiced])

...couple of mouse clicks and I have average turn-days as part of all the report drill down levels on billings reports.

Can't you make it harder? I must never show anyone here how little work I must actually do in these reports. They might get some wild idea they could do it themself!

Bob