How to always correct the date to the 1st.

eriena
eriena Member
in Magic ETL

Hello all,

I want to know the code for ETL "Add Formmura" function.

The column ”Data” will always contain a value for the year, month, and date.

When the value comes in , I want to set the date value always to 1st by using " Add Formmura" function in ETL as like below picture.

Could you let me know the code to enter into Add Formmura?


image.png
image.png


Thank you.

Tagged:

Best Answer

  • eriena
    eriena Member
    Answer ✓

    I found the answer.


    DATE_FORMAT(`Date`, '%Y-%m-01')

    The code solve my question.


    Thank you @GrantSmith

Answers

  • GrantSmith
    GrantSmith Coach

    There’s many ways to do this. One way is to select the last day of the month and then subtract the day number of the month minus one

    LAST_DAY(`Date`) - INTERVAL (DAYOFMONTH(`Date`) - 1) DAY
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • eriena
    eriena Member

    @GrantSmith Thank you for your advice. I tried the code, but it worked as below.

    2022/4/12 --> 2022/4/19

    2022/4/21 --> 2022/4/10

    I want to write code so that if a date other than the 1st come in, it will still be the 1st as below.

    2022/4/12 --> 2022/4/1

    2022/4/21 --> 2022/4/1

    Thank you.

  • eriena
    eriena Member
    Answer ✓

    I found the answer.


    DATE_FORMAT(`Date`, '%Y-%m-01')

    The code solve my question.


    Thank you @GrantSmith

Categories