Date formatting in ETL

Victoria_G1

Hello everyone,

I am trying to remove the 0 to format my dates as in the following screenshot:

Any advice?

Thanks!

RobSomers

@Victoria_G1 Use a formula tile and the DATE_FORMAT() function to change it. Here's a link on how to use it (%c will give you the month number without a leading 0):

https://www.w3schools.com/sql/func_mysql_date_format.asp

MichelleH

@Victoria_G1 Just to confirm, are you trying to remove leading zeros from the month portion? In other words, should the second row in your screenshot change from "2023-04" to "2023-4"? If so, you could use a formula tile to remove the zeros like this:

REPLACE(`Date`,'-0','-')

However, note that removing the zeros could potentially cause issues when sorting by Date since it is a text field and not a date or number.

ellibot

@Victoria_G1 have you tried adjusting the date formatting on the card level? I find this is easier compared to adjusting it in the ETL or beastmode.

Victoria_G1

Thank you so much! One additional query: how do I change a string in this format to a date data type?

MarkSnodgrass

You should be able to use the CONCAT to add a day on the end, such as 1 and then convert it to a date. Like this:

DATE(CONCAT(datefield,'-01'))