How to always correct the date to the 1st.

eriena

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.

eriena

I found the answer.

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

The code solve my question.

Thank you @GrantSmith

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

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.

eriena

I found the answer.

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

The code solve my question.

Thank you @GrantSmith