date format in analytics report is throwing error

[Harsha]

Hi Team,

I am trying to create analytics report to use it in dashbaord for terminated users from last 30 days the below highlighted format is working fine for onboarded in last 30 days but for termination its throwing error like below:
2023-06-26T06:19:36.932004746Z stdout F [0]: index [testharsha], type [analytics], id [O_9b9ogBdZR6O4qzlH5e], message [ElasticsearchException[Elasticsearch exception [type=mapper_parsing_exception, reason=failed to parse field [TerminationDate] of type [date] in document with id 'O_9b9ogBdZR6O4qzlH5e']]; nested: ElasticsearchException[Elasticsearch exception [type=illegal_argument_exception, reason=Invalid format: "2023-06-14" is too short]];]
023-06-26T06:19:36.932154346Z stdout F [0]: index [testharsha], type [analytics], id [O_9b9ogBdZR6O4qzlH5e], message [ElasticsearchException[Elasticsearch exception [type=mapper_parsing_exception, reason=failed to parse field [TerminationDate] of type [date] in docum...
Query for terminated users for last 30 days:
select DISTINCT u.USERKEY AS 'USERCONTEXT',u.employeeid EmployeeNumber, u.firstname FirstName,u.lastname LastName, U.customproperty23 as UserCountry , u.systemusername Systemusername ,u.statuskey  as userstatus, startdate as StartDate,u.createdate, u.CUSTOMPROPERTY62 as TerminationDate FROM USERS U WHERE (STR_TO_DATE(DATE_FORMAT(curdate(),'%d-%m-%Y'),'%d-%m-%Y')-STR_TO_DATE(DATE_FORMAT(u.CUSTOMPROPERTY62,'%d-%m-%Y'),'%d-%m-%Y')) <= 30 and customproperty30='A'

PS: I have tried different function yet the error is persistent.Please let me know if anyone has faced same issue, if yes how did you fix it.

Thank you,
Harsha

[armaanzahir · Accepted solution]

Hi Harsha,

There is sometimes an issue with the ESService parsing an alias which has the suffix set as "Date". Kindly try setting the alias to "DateTermination" and try again.

Thanks,

Armaan

[armaanzahir · Accepted solution]

select DISTINCT u.USERKEY AS 'USERCONTEXT',u.employeeid EmployeeNumber, u.firstname FirstName,u.lastname LastName, U.customproperty23 as UserCountry , u.systemusername Systemusername ,u.statuskey  as userstatus, startdate as DateStart,u.createdate, u.CUSTOMPROPERTY62 as DateTermination FROM USERS U WHERE (STR_TO_DATE(DATE_FORMAT(curdate(),'%d-%m-%Y'),'%d-%m-%Y')-STR_TO_DATE(DATE_FORMAT(u.CUSTOMPROPERTY62,'%d-%m-%Y'),'%d-%m-%Y')) <= 30 and customproperty30='A'

The above query should work.

[Harsha]

Hi @armaanzahir ,

Thank you so much, it worked and solved the issue.

Thank you,

Harsha