How to always correct the date to the 1st.

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?

Thank you.


Best Answer

  • eriena
    Answer ✓

    I found the answer.

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

    The code solve my question.

    Thank you @GrantSmith


  • 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!**
  • @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.