# CustomFunction on the Date field

**URL:** <https://community.spicecrm.io/t/customfunction-on-the-date-field/798>\
**Category:** KReporter\
**Created:** [August 5, 2026, 7:13pm UTC](https://community.spicecrm.io/t/customfunction-on-the-date-field/798 "2026-08-05T19:13:09Z")\
**Posts on this page:** 2\
**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:** [August 5, 2026, 7:13pm UTC](https://community.spicecrm.io/t/customfunction-on-the-date-field/798/1 "2026-08-05T19:13:09Z")

</div>

### Only Year

1. **`DATE_FORMAT({t}.{f}, '%Y')`** → Returns a 4-digit year (e.g., `2026`).
2. **`DATE_FORMAT({t}.{f}, '%y')`** → Returns a 2-digit year (e.g., `26`).
3. **`YEAR({t}.{f})`** → A direct alternative function to extract the year as a numeric value.

### Year-Month Variations

1. **`DATE_FORMAT({t}.{f}, '%Y-%m')`** → Standard numeric year-month (e.g., `2026-08`).
2. **`DATE_FORMAT({t}.{f}, '%Y/%m')`** → Slash-separated year-month (e.g., `2026/08`).
3. **`DATE_FORMAT({t}.{f}, '%M %Y')`** → Full month name and year (e.g., `August 2026`).
4. **`DATE_FORMAT({t}.{f}, '%b-%y')`** → Short month text and 2-digit year (e.g., `Aug-26`)

### Week Variations

1. **`DATE_FORMAT({t}.{f}, '%Y-%u')`** → Year and week number, where Monday is the first day of the week (e.g., `2026-31`).
2. **`DATE_FORMAT({t}.{f}, '%Y-%v')`** → Year and week number (ISO 8601 standard, where week starts on Monday and week 1 is the first week with 4+ days).
3. **`DATE_FORMAT({t}.{f}, '%Y-%U')`** → Year and week number, where Sunday is the first day of the week.
4. **`WEEK({t}.{f})`** → A direct alternative function to extract just the numeric week number of the year.

---

<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:** [August 5, 2026, 7:16pm UTC](https://community.spicecrm.io/t/customfunction-on-the-date-field/798/2 "2026-08-05T19:16:23Z")

</div>

### Month-Day Variations

1. **`DATE_FORMAT({t}.{f}, '%m-%d')`** → Numeric month and day (e.g., `08-25`).
2. **`DATE_FORMAT({t}.{f}, '%b %d')`** → Short month name and numeric day (e.g., `Aug 25`).
3. **`DATE_FORMAT({t}.{f}, '%M %e')`** → Full month name and day without leading zero (e.g., `August 25`).
4. **`DATE_FORMAT({t}.{f}, '%d/%m')`** → European slash format (e.g., `25/08`).

### Day Only Variations

1. **`DATE_FORMAT({t}.{f}, '%d')`** → Day of the month with a leading zero (e.g., `01` to `31`).
2. **`DATE_FORMAT({t}.{f}, '%e')`** → Day of the month without a leading zero (e.g., `1` to `31`).
3. **`DATE_FORMAT({t}.{f}, '%W')`** → Full weekday name (e.g., `Tuesday`).
4. **`DATE_FORMAT({t}.{f}, '%a')`** → Short weekday name (e.g., `Tue`).
5. **`DATE_FORMAT({t}.{f}, '%w')`** → Day of the week as a number where Sunday is 0 (e.g., `0` to `6`).
6. **`DATE_FORMAT({t}.{f}, '%j')`** → Day of the year as a three-digit number (e.g., `001` to `366`)