Date formatting in ETL

Options
Victoria_G1
Victoria_G1 Member
in Magic ETL

Hello everyone,

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

image.png

Any advice?

Thanks!

Answers

  • RobSomers
    RobSomers Coach
    Options

    @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

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • MichelleH
    MichelleH Coach
    Options

    @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
    ellibot Contributor
    Options

    @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
    Victoria_G1 Member
    Options

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

  • MarkSnodgrass
    MarkSnodgrass Coach
    Options

    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'))

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Categories