formatting date in email template

[shivmano]

Hi Team,

I am fetching the entitlement expiry date using analytics to notify end users using the below variable

${ANALYTICSDATA.'End Date'[0]} where 'End Date' is the value for the entitlement end date in the sql query. this is printing the end date in the format YYYY-MM-DD HH:MM:SS by default and I want to be able to print the end date in the email as MMM dd,yyyy instead. 

Please can someone advise how can the format be changed for the above variable? I have tried to change the format in the SQL query itself using the DATE_FORMAT function but the template is throwing error when I do that. 

[0]: index [testshiva], type [analytics], id [moE6NYwBSrr57YP5WlNs], message [ElasticsearchException[Elasticsearch exception [type=mapper_parsing_exception, reason=failed to parse field [End Date] of type [date] in document with id 'moE6NYwBSrr57YP5WlNs']]; nested: ElasticsearchException[Elasticsearch exception [type=illegal_argument_exception, reason=Invalid format: "Dec 04,2023"]];]

I have tried ${ANALYTICSDATA.'End Date'[0].format('MMM dd,yyyy')} and ${ANALYTICSDATA.'End Date'[0].toString().format('MMM dd,yyyy')} but none works. in this case, it ignores the value and just prints the output as MMM dd,yyyy instead of actual value. 

[sk]

@shivmano : Instead of trying to format in email template why don't try to format in SQL itself? like below

DATE_FORMAT(<date column>, "%b %d, %Y")

[shivmano]

@sk thank you for the response. Like I mentioned, I have actually tried to format it using DATE_FORMAT in the query itself. but it throws below error when I do that 

[0]: index [testshiva], type [analytics], id [moE6NYwBSrr57YP5WlNs], message [ElasticsearchException[Elasticsearch exception [type=mapper_parsing_exception, reason=failed to parse field [End Date] of type [date] in document with id 'moE6NYwBSrr57YP5WlNs']]; nested: ElasticsearchException[Elasticsearch exception [type=illegal_argument_exception, reason=Invalid format: "Dec 04,2023"]];]

[sk · Accepted solution]

@shivmano : Did you use the same format I shared? Can you share your sql query? Also don't alias the name which ends as Date, So change accordingly

[shivmano]

Yes, Here is the query 

select u.username as 'username', u.firstname as 'FirstName', u.lastname as 'LastName', a.name as 'AccountName', e.displayname as 'Application', ev.displayname as 'Entitlement', ev.DESCRIPTION as 'Description' , DATE_FORMAT(ae1.ENDDATE, '%b %d,%Y') as 'End Date' from users u, accounts a, user_accounts ua, entitlement_values ev, account_entitlements1 ae1, endpoints e where u.userkey= ua.userkey and u.STATUSKEY=1 and ua.ACCOUNTKEY = a.ACCOUNTKEY and a.ACCOUNTKEY = ae1.ACCOUNTKEY and a.status in ("Manually Provisioned",1,'Active') and ae1.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY and a.ENDPOINTKEY = e.ENDPOINTKEY and e.STATUS = 1 and ev.status = 1 and ae1.enddate is not null and (ae1.ASSIGNEDFROMROLE is null or ae1.ASSIGNEDFROMROLES is null) and DATEDIFF(ae1.ENDDATE,sysdate()) between 0 and 7

[sk]

Try below

select u.username as 'username', u.firstname as 'FirstName', u.lastname as 'LastName', a.name as 'AccountName', e.displayname as 'Application', ev.displayname as 'Entitlement', ev.DESCRIPTION as 'Description' , DATE_FORMAT(ae1.ENDDATE, '%b %d,%Y') as 'End_Date' from users u, accounts a, user_accounts ua, entitlement_values ev, account_entitlements1 ae1, endpoints e where u.userkey= ua.userkey and u.STATUSKEY=1 and ua.ACCOUNTKEY = a.ACCOUNTKEY and a.ACCOUNTKEY = ae1.ACCOUNTKEY and a.status in ("Manually Provisioned",1,'Active') and ae1.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY and a.ENDPOINTKEY = e.ENDPOINTKEY and e.STATUS = 1 and ev.status = 1 and ae1.enddate is not null and (ae1.ASSIGNEDFROMROLE is null or ae1.ASSIGNEDFROMROLES is null) and DATEDIFF(ae1.ENDDATE,sysdate()) between 0 and 7

[shivmano]

Thank you @sk , looks like the issue was with using the alias that ends with 'Date'. Changing the alias in the query worked..!

[rushikeshvartak]

Change SQL Query

select u.username, u.firstname, u.lastname, a.name as 'Account Name',  e.endpointname as 'Application', ev.entitlement_value as 'Entitlement', ev.DESCRIPTION as 'Description' ,
DATE_FORMAT(ae1.ENDDATE ,"%M %d,%Y") as 'End_Date' from
users u, accounts a, user_accounts ua, entitlement_values ev, account_entitlements1 ae1, endpoints e
where u.userkey= ua.userkey and u.STATUSKEY=1
and ua.ACCOUNTKEY = a.ACCOUNTKEY
and a.ACCOUNTKEY = ae1.ACCOUNTKEY
and a.status in ("Manually Provisioned",1,'Active')
and ae1.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY
and a.ENDPOINTKEY = e.ENDPOINTKEY
and e.STATUS = 1
and ev.status = 1
and ae1.enddate is not null
and (ae1.ASSIGNEDFROMROLE is null or ae1.ASSIGNEDFROMROLES is null)
and DATEDIFF(ae1.ENDDATE,sysdate()) between 0 AND (select configdata from configuration where name = 'NUMBEROFDAYSBEFOREENTITLEMENTEXPIRYDATE');

[shivmano]

Like I mentioned, I have actually tried to format it using DATE_FORMAT in the query itself. but it throws below error when I do that 

[0]: index [testshiva], type [analytics], id [moE6NYwBSrr57YP5WlNs], message [ElasticsearchException[Elasticsearch exception [type=mapper_parsing_exception, reason=failed to parse field [End Date] of type [date] in document with id 'moE6NYwBSrr57YP5WlNs']]; nested: ElasticsearchException[Elasticsearch exception [type=illegal_argument_exception, reason=Invalid format: "Dec 04,2023"]];]

[rushikeshvartak]

change alias of column