How to always correct the date to the 1st.

eriena
eriena 🟡
in Data Platform & Data Science

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 🟡
    Answer ✓

    I found the answer.


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

    The code solve my question.


    Thank you @GrantSmith

Answers

  • GrantSmith
    GrantSmith Indiana 🥷

    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 🟡

    @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.

Categories