Hi!

I've got a table containing logs and I would like to perform some aggregate functions with a group by that trims the date.

Here's the SQL equivalent of what I want:

select convert(datetime, cast(datepart(day,viewed_on) as varchar) + '.' + cast(datepart(month,viewed_on) as varchar) + '.' + cast(datepart(year,viewed_on) as varchar), 104) as trimmed_date, sum(id)
from
employer_view;

Cheers, Nitin

PS: The trimmed date sets the hour and minute to 0.

Use a dynamicList, with DBFunctionCall expression applied to the required field, to implement the convert function.

Then use the same field in a groupby clause.

I'd use the following expression on a Resultset field (e.g. ConsolidationValue) in a dynamic typed list :

fields("ConsolidationValue").ExpressionToApply = New DbFunctionCall("CAST(FLOOR(CAST({0} AS FLOAT)) AS DATETIME)", New Object() {TransactionDetailFields.DateTransaction})

TransactionDetailFields.DateTransaction is a DateTime field in a table.

grtz, Danny