Oracle Communities

Oracle Analytics and AI

Child Item

How to populate employee start date with current year?

I have created a field which is extracting the day and month from an employee's start date and combining with the current year (see below). I am having trouble casting this back as a date field can anyone advise how I would do this or a better way to extract day/month from start date and combine with the current year?

Thanks, Scott

Additional Info: The field is populating as expected (see below Anniversary Date) but is populating as an Attribute and I can not convert to a Date field.

Find more posts tagged with

FDI

Fusion HCM Analytics

Accepted answers

Orange

Hi @Scott_Turner_ ,

Please try the following calculation:

CASE

WHEN TIMESTAMPADD(SQL_TSI_YEAR, CAST(YEAR(CURRENT_DATE) - YEAR(Legal Employer Seniority Date) AS INT) , Legal Employer Seniority Date) <= CURRENT_DATE

THEN TIMESTAMPADD(SQL_TSI_YEAR, CAST(YEAR(CURRENT_DATE)+1 - YEAR(Legal Employer Seniority Date) AS INT) , Legal Employer Seniority Date)

ELSE TIMESTAMPADD(SQL_TSI_YEAR, CAST(YEAR(CURRENT_DATE) - YEAR(Legal Employer Seniority Date) AS INT) , Legal Employer Seniority Date)

END

Kind regards

All comments

Mallikarjuna Kuppauru-Oracle

Hi @Scott_Thomson

You can try to maintain all the dimension columns in same format and then use MONTHNAME and DAY NAME and YEAR to concatenate the extracted string.

Regards,

Arjun

Scott_Turner_

Hi @Mallikarjuna Kuppauru-Oracle

I am not sure what you mean, I have extracted all and concatenated but am struggling to revert to a date field. Note: I add a bit more info in initial query.

Thanks

Scott