# How to extract year & month from date

**URL:** <https://community.spicecrm.io/t/how-to-extract-year-month-from-date/716>\
**Category:** KReporter\
**Created:** [January 11, 2024, 8:38pm UTC](https://community.spicecrm.io/t/how-to-extract-year-month-from-date/716 "2024-01-11T20:38:23Z")\
**Posts on this page:** 12\
**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:** [January 11, 2024, 8:38pm UTC](https://community.spicecrm.io/t/how-to-extract-year-month-from-date/716/1 "2024-01-11T20:38:23Z")

</div>

Hello,

I have date field like `05/18/2018` 09:12AM or `2023-10-03 18:11:08`.

I want to display only year out of it. I am using the below **Formula** on the _manipulate_ screen. But it is not working.

> substr(0,4)

> substr(5,2)

Please provide CustomFunction or Formula to achieve it.

---

<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:** [January 12, 2024, 12:48pm UTC](https://community.spicecrm.io/t/how-to-extract-year-month-from-date/716/2 "2024-01-12T12:48:29Z")

</div>

@rsp easiest is to use a custom SQL date\_format to extract parts of the date,  
Example for Year:

1. Drag your date field to manipulate

2. Add DATE\_FORMAT({t}.{f}, ‘%Y’) in custom Function  

3. under tab presentation set override\_type to text

---

<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:** [January 12, 2024, 5:02pm UTC](https://community.spicecrm.io/t/how-to-extract-year-month-from-date/716/3 "2024-01-12T17:02:20Z")

</div>

Still not getting output table.

 ![image](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/0e76125175b81659c64baa00415eca4992ea06c3.png)

 ![image](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/00d6d4f89c436a8a17d41690c0ad6f610871ace2.png)

---

<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:** [January 12, 2024, 6:27pm UTC](https://community.spicecrm.io/t/how-to-extract-year-month-from-date/716/4 "2024-01-12T18:27:40Z")

</div>

@rsp Is that a KReporter 3.1 or 3.6?  
Then no need to overwrite the Type in presentation.  
If you have the Query analyzer option under Integrate, then turn it on and check the query built.  
If you don’t then you will have to grab the query yourself by hacking the code. modules/KReports/KReport.php in function getContextselectionResult where you’ll find something like  
$query = $this-\>get\_report\_main\_sql\_query(true, $additionalFilter, $additionalGroupBy, $parameters);  
The built query will be in $query.  
Good luck!

---

<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:** [January 12, 2024, 8:28pm UTC](https://community.spicecrm.io/t/how-to-extract-year-month-from-date/716/5 "2024-01-12T20:28:43Z")

</div>

- 3.1

- Enabled query analyzer: here is query. But I am getting blank page.

> SELECT tdynqphxnxk.id as “sugarRecordId”, ‘Leads’ as “sugarRecordModule” , cmedkzdxsza.id as “cmedkzdxszaid”, ‘root:Leads:🔗Leads:opportunity’ as “cmedkzdxszapath”, tdynqphxnxk.id as “tdynqphxnxkid”, ‘root:Leads’ as “tdynqphxnxkpath”, (DATE\_FORMAT(tdynqphxnxk.date\_entered, %u2018%Y%u2019)) as “k7832e42e26e92a67e81f1e02ce44”, (DATE\_FORMAT(tdynqphxnxk.date\_entered, %u2018%M%u2019)) as “k75fd940316214d53431930433082”, tdynqphxnxk.date\_entered as “kaf28984cfe4a1d88285796cd2820”, COUNT(tdynqphxnxk.lead\_source) as “ka5ee9d2403097b0313d34dd3a8f5”, SUM(cmedkzdxsza.amount) as “k0e7afed8bcad1ea5374d1eb43468”, ‘-99’ as ‘k0e7afed8bcad1ea5374d1eb43468\_curid’ FROM leads tdynqphxnxk LEFT JOIN leads\_cstm as xyzpjcpsesf ON tdynqphxnxk.id = xyzpjcpsesf.id\_c INNER JOIN opportunities cmedkzdxsza ON tdynqphxnxk.opportunity\_id=cmedkzdxsza.id AND cmedkzdxsza.deleted=0 LEFT JOIN opportunities\_cstm as xsjpepaymgp ON cmedkzdxsza.id = xsjpepaymgp.id\_c WHERE tdynqphxnxk.deleted = ‘0’ AND ((tdynqphxnxk.date\_entered \> ‘2016-12-31 0:00:00’)) GROUP BY (DATE\_FORMAT(tdynqphxnxk.date\_entered, %u2018%Y%u2019)), (DATE\_FORMAT(tdynqphxnxk.date\_entered, %u2018%M%u2019)) ORDER BY tdynqphxnxk.id ASC

---

<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:** [January 14, 2024, 12:08pm UTC](https://community.spicecrm.io/t/how-to-extract-year-month-from-date/716/6 "2024-01-14T12:08:53Z")

</div>

@rsp The query as displayed in the thread contains syntax errors .

```auto
(DATE_FORMAT(tdynqphxnxk.date_entered, %u2018%Y%u2019))

```

cannot work. %u2018 and %u2019 are the represention for the single quote.

That is what the query should look like.

```auto
SELECT tdynqphxnxk.id AS 'sugarRecordId', 'Leads' AS 'sugarRecordModule', 
cmedkzdxsza.id AS 'cmedkzdxszaid', 
'root:Leads::link:Leads:opportunity' AS 'cmedkzdxszapath', 
tdynqphxnxk.id AS 'tdynqphxnxkid', 'root:Leads' AS 'tdynqphxnxkpath', 
(DATE_FORMAT(tdynqphxnxk.date_entered, '%Y')) AS 'k7832e42e26e92a67e81f1e02ce44', 
(DATE_FORMAT(tdynqphxnxk.date_entered, '%M')) AS 'k75fd940316214d53431930433082', 
tdynqphxnxk.date_entered AS 'kaf28984cfe4a1d88285796cd2820', 
COUNT(tdynqphxnxk.lead_source) AS 'ka5ee9d2403097b0313d34dd3a8f5', 
SUM(cmedkzdxsza.amount) AS 'k0e7afed8bcad1ea5374d1eb43468', 
'-99' AS 'k0e7afed8bcad1ea5374d1eb43468_curid'
FROM leads tdynqphxnxk
LEFT JOIN leads_cstm AS xyzpjcpsesf ON tdynqphxnxk.id = xyzpjcpsesf.id_c
INNER JOIN opportunities cmedkzdxsza ON tdynqphxnxk.opportunity_id=cmedkzdxsza.id 
AND cmedkzdxsza.deleted=0
LEFT JOIN opportunities_cstm AS xsjpepaymgp ON cmedkzdxsza.id = xsjpepaymgp.id_c
WHERE tdynqphxnxk.deleted = '0' AND ((tdynqphxnxk.date_entered > '2016-12-31 0:00:00'))
GROUP BY (DATE_FORMAT(tdynqphxnxk.date_entered, '%Y')), 
(DATE_FORMAT(tdynqphxnxk.date_entered, '%M'))
ORDER BY tdynqphxnxk.id ASC

```

Run it directly in your database to see if you get records.  
If you get records, then try to use the double instead of single quote in the DATE\_FORMAT custom function field. They might be rendered properly.  
Or you debug and fix the bad conversion from json that apparently is in 3.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:** [January 17, 2024, 9:55pm UTC](https://community.spicecrm.io/t/how-to-extract-year-month-from-date/716/7 "2024-01-17T21:55:21Z")

</div>

Thank you so much 😁

I used double quote in the DATE\_FORMAT and it worked…

---

<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:** [January 18, 2024, 5:46pm UTC](https://community.spicecrm.io/t/how-to-extract-year-month-from-date/716/8 "2024-01-18T17:46:52Z")

</div>

How to use this custom function and apply Average function on it?

**Formula -** Round(avgDays)

> datediff({tc}.date\_modified,{tc}.date\_created)

```auto
04/10/2017 02:47PM

```

```auto
06/17/2019 09:12AM

```

---

<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:** [January 19, 2024, 8:56am UTC](https://community.spicecrm.io/t/how-to-extract-year-month-from-date/716/9 "2024-01-19T08:56:45Z")

</div>

@rsp You want to calculate the average of days regarding what? The average option under function night do the trick already. Or you can do it per custom function in SQL (check SQL documentation to get AVG() working of write your own maths) or you do it per php calculation.

---

<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:** [January 19, 2024, 3:08pm UTC](https://community.spicecrm.io/t/how-to-extract-year-month-from-date/716/10 "2024-01-19T15:08:33Z")

</div>

So, in KReporter, I want to calculate average days take to complete that particular lead.

I have date\_created and date\_modified columns in the my database.

So, how to find date difference in the kreporter?

---

<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:** [January 21, 2024, 10:27am UTC](https://community.spicecrm.io/t/how-to-extract-year-month-from-date/716/11 "2024-01-21T10:27:34Z")

</div>

Why use the leads\_cstm table?  
Best would be the leads\_audit as you can make sure when the lead status was to set to converted.  
But let say that you can get an approximate value using date\_entered and date\_modified.  
datediff({t}.date\_modified,{t}.date\_entered)

For average you’ll have to group somewhere since you need the total of leads.  
I guess easiest to write a full custom SQL getting the information. It won’t perform on big data.

---

<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:** [May 29, 2026, 3:37pm UTC](https://community.spicecrm.io/t/how-to-extract-year-month-from-date/716/12 "2026-05-29T15:37:13Z")

</div>

# Solution ✅

> DATE\_FORMAT({t}.{f}, “%Y-%m”)

> DATE\_FORMAT({t}.{f}, ‘%Y-%m’)