How to extract year & month from date

KReporter
1

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.

2

@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

    image
    image1295×658 31.4 KB

  3. under tab presentation set override_type to text

3

Still not getting output table.

image
image1198×280 22.1 KB

image
image1108×375 23.8 KB

4

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

5

  • 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::link: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

6

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

(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.

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.

1 Like
7

Thank you so much :grin:

I used double quote in the DATE_FORMAT and it worked…

8

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

Formula - Round(avgDays)

datediff({tc}.date_modified,{tc}.date_created)

04/10/2017 02:47PM

06/17/2019 09:12AM
9

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

10

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?

11

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.

12

Solution :white_check_mark:

DATE_FORMAT({t}.{f}, “%Y-%m”)

DATE_FORMAT({t}.{f}, ‘%Y-%m’)