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

Mike Kiser 1,536 Reputation points
2021-03-19T00:21:09.82+00:00

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

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,073 questions
0 comments No comments
{count} votes

Accepted answer
  1. Nasreen Akter 10,791 Reputation points
    2021-03-19T01:26:15.337+00:00

    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!


0 additional answers

Sort by: Most helpful