question

ewinkiser avatar image
0 Votes"
ewinkiser asked nasreen-akter commented

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

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

nasreen-akter avatar image
0 Votes"
nasreen-akter answered nasreen-akter commented

Hi @ewinkiser,

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://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/format-datetimefunction#supported-formats

Thanks!

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Wow, @nasreen-akter , you are amazing with your knowledge of ADF!

The coalesce worked the first time. I have never used it before. HOWEVER, I need it to be in this format for Ceridian: "2021-02-12T14:06:30.008Z", Currently is it in "2/1/09" when I send it over in my REST API call and the HTTP PATCH call fails. I experimented with some other formats but can not get it right. Any suggestions?

Thanks so much again!!
Mike



79443-screenshot-2021-03-18-222530.jpg79439-screenshot-2021-03-18-223149.jpg

79457-image.png


0 Votes 0 ·

Hi @ewinkiser, would you please add another dateFormat and try again e.g.,

 @formatDateTime(formatDateTime(coalesce(item().savingsPlanVestingDate, item().benefitsEligibilityStartDate), 'MM/01/yyyy'), 'o')

Thanks! :)

0 Votes 0 ·