Question 103504 in Azure Question posted in
The official documentation to get you started can be found here.

How to convert YYYYMM to YYYY-MM-01 using Azure data factory expression

I need to convert the YYYYMM to YYYY-MM-01 using azure data factory pipeline expression.

I tried the below expression but it giving me error that date value should follow the ISO 8601 format.

@formatDateTime(concat('202301','01'),'YYYY-MM-DD')

It should return in this format ‘2023-01-01’.

Thanks

Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    I used multiple substring to get this as shown below, if any better answer please let me know.

    @concat(substring(concat('202301','01'),0,4 ),'-',substring(concat('202301','01'),4,2),'-',substring(concat('202301','01'),6,2))


  2. 0

    You can supply a format string including numbers to formatDateTime, for example:

    @formatDateTime(utcnow(), 'yyyy-MM-01')

    NB You have the case of your argument wrong, it should be lower-case y for Year, upper-case M for month and lower-case d for Day.

    Login or Signup to reply.
  3. 0

    Instead of concatenating 202301 with 01 (three times) and then taking substring, you can use the below expression to achieve the same.

    @concat(substring('202301',0,4 ),'-',substring('202301',4,2),'-','01')

    enter image description here

    enter image description here

    Login or Signup to reply.
  4. 0

    Just a simpler way , then what you have used.

    @concat(substring('202301',0,4),'-',substring('202301',4,2),'-01')

    enter image description here

    The issue here is the

    @formatDateTime(string(concat('202301','01')),'YYYYMMDD' ) does not work

    is missing the "-" but

    @formatDateTime(string(concat('2023-01','-01')),'yyyyMMdd' )

    Just works fine.

    I am not sure as to what is the Source here , if it is query then you can get the yyyymm in the form of yyyy-mm and that should help .

    Also I request to have a look on the startOfMonth function .

    https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#startOfMonth

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search