# How to get year or month from field?

**URL:** <https://community.spicecrm.io/t/how-to-get-year-or-month-from-field/784>\
**Category:** KReporter\
**Created:** [November 3, 2025, 3:09pm UTC](https://community.spicecrm.io/t/how-to-get-year-or-month-from-field/784 "2025-11-03T15:09:07Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![rsp](https://avatars.discourse-cdn.com/v4/letter/r/4491bb/32.png) [@rsp](https://community.spicecrm.io/u/rsp)\
**Post date:** [November 3, 2025, 3:09pm UTC](https://community.spicecrm.io/t/how-to-get-year-or-month-from-field/784/1 "2025-11-03T15:09:07Z")

</div>

@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)

---

<div class="post-metadata">

**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\
**Post date:** [November 3, 2025, 3:42pm UTC](https://community.spicecrm.io/t/how-to-get-year-or-month-from-field/784/2 "2025-11-03T15:42:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rsp](https://avatars.discourse-cdn.com/v4/letter/r/4491bb/32.png) [@rsp](https://community.spicecrm.io/u/rsp)\
**Post date:** [November 3, 2025, 3:53pm UTC](https://community.spicecrm.io/t/how-to-get-year-or-month-from-field/784/3 "2025-11-03T15:53:29Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\
**Post date:** [November 3, 2025, 4:19pm UTC](https://community.spicecrm.io/t/how-to-get-year-or-month-from-field/784/4 "2025-11-03T16:19:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rsp](https://avatars.discourse-cdn.com/v4/letter/r/4491bb/32.png) [@rsp](https://community.spicecrm.io/u/rsp)\
**Post date:** [November 3, 2025, 4:24pm UTC](https://community.spicecrm.io/t/how-to-get-year-or-month-from-field/784/5 "2025-11-03T16:24:50Z")

</div>

Got it, thanks a lot for help! 😃