Changing an existing date to the first of the month date in a pipeline

Question

Hello! @Nasreen Akter @MartinJaffer-MSFT @HimanshuSinha-msft @MelissaMa-MSFT

Our ADF Pipelines are in QA Testing getting ready for GO LIVE. One of the pipelines had the following requirements added by a User:

Alternate Vesting Date (EmployeePropertyXrefCode9004) - Date/Time - SFfield (EmpEmployment.CustomDate2. Logic needs to be added - if no value exists in this field in SF, then pull EmpEmployment.benefitsEligibilityStartDate, if the BenefitsEligibilityStartDate is not the first of the month change to use the first of the month. (ex. if it is 8/17/20 please send 8/1/20))

The date shows up in the pipeline as "benefitsEligibilityStartDate": "2011-09-12T00:00:00+00:00",
for example.

How can I change the date to the first day of the month in a variable? I have tried but unsuccessfully.

Thanks!!
MIke Kiser

Answer 1

Hi @Mike Kiser ,

You can try formatDateTime e.g.,

@formatDateTime('2011-09-12T00:00:00+00:00', 'M/1/yy')  

@formatDateTime('2011-09-12T00:00:00+00:00', 'MM/01/yyyy')  
  
@formatDateTime(coalesce(EmpEmployment.CustomDate2, EmpEmployment.benefitsEligibilityStartDate), 'M/1/yy')  

Please check the below link for date formats:
https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/format-datetimefunction#supported-formats

Thanks!