How to get year or month from field?

KReporter
1

@maretval how to get the year or month out of date time field in the kreport.

For example, the date field has value 10/20/2030 11:11pm.

In the formula, column we tried to use the below but did not work.

substr({Stored Value},0,4)

2

Format the datetime field directly in custom function

YEAR({t}.{f}) will extract the year of the dragged field (represented by {t}.{f}.
Think of changing the type to string or number in the presentation > override

or use DATE_FORMAT({t}.{f}, ‘%Y-%m‘) for more complex display => 2030-10
Think of changing the type to string in the presentation > override

Check MySQL documentation on datetime fields for possible formats

1 Like
3

I used YEAR({t}.{f}) to get year and MONTH({t}.{f}) to get month, but it did not work.

I think if I use below at CustomFormula, it is working:

DATE_FORMAT({tc}.FIELD, ‘%Y’)

DATE_FORMAT({tc}.FIELD, ‘%m’)

Is it correct one?

4

Still using an old SuiteCRM?
{tc} is for the old custom table when you have custom fields created with the studio.

In the Reporter:
custom function is for the mySQL Query
Formula is just PHP thta can be applied on the results.

Easier is the custom function.
Under Manipulate {t}.{f} will represent the field you dragged.
Under Filter you will have to write the field name even if dragged. {t}.date_modified

1 Like
5

Got it, thanks a lot for help! :smiley: