Convert text to date field using

Newbie23
Newbie23 Member
in Magic ETL

Hello,

I am using Magic ETL and trying to convert a text field to a date field.

Earlier on my data flow I had to alter some columns by using the cast function.

image.png

I was able to continue with the data flow and have the output I want. It was until I was creating a card that I noticed the "Date" is "Text". (I have some formulas and rename the column "RRDAT" to Month"

image.png

When I try to do my cards, I don't have the time range to select the time frame for my chart

image.png

Any idea how I can convert this "Month" column from text to date?

Best Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Since your field only contains the month and the year, I recommend using the split_part and concat function to pull out the month and year and set it to the 1st of the month. You can do it like this:

    DATE(CONCAT(split_part(Monthfield,'-',1),'/1/',split_part(Monthfield,'-',2)))

    Wrapping the DATE() function around all of it will convert it to a date field after it has built the date.

    **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.
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    First I would make sure that the column is included in the select columns tile at the end of the ETL. Next, I would edit the card in Analyzer and click on the Date Range selector in the top right. Ensure that Hide Date on Card Details is not selected and that the appropriate Date Range Field is selected.

    image.png

    **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.
  • Newbie23
    Newbie23 Member
    Answer ✓

    Thanks Mark and Grand for your support, I was able to get the Data Range on my chart, it appears I didn't have all the columns selected at the end of the ETL.

    image.png

Answers

  • GrantSmith
    GrantSmith Coach

    You can use a formula tile and the STR_TO_DATE function

    STR_TO_DATE(`Month`, '%Y-%m')

    Alternatively you could just use LAST_DAY to get the last day of a month to get the month of your date / timestamp field.

    LAST_DAY(`RRDAT`)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Newbie23
    Newbie23 Member

    Still missing something, when I validate the formula shows that it is valid, but I get the error message.

    I can not use your second suggestion as 'RRDAT' was rename.

    image.png

  • GrantSmith
    GrantSmith Coach

    Sorry, I had the wrong format, try %m-%Y.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Newbie23
    Newbie23 Member

    still not working, getting the same error

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Since your field only contains the month and the year, I recommend using the split_part and concat function to pull out the month and year and set it to the 1st of the month. You can do it like this:

    DATE(CONCAT(split_part(Monthfield,'-',1),'/1/',split_part(Monthfield,'-',2)))

    Wrapping the DATE() function around all of it will convert it to a date field after it has built the date.

    **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.
  • Newbie23
    Newbie23 Member

    Thanks Mark, for the suggestion, so the formula works, but when I try to visualize the data by creating a card I don't see the date range at the top of my chart, see below chart.

    I want to be able to have the "date range" option.

    I haven't been able to find a way to add this option to my card.

    image.png

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    First I would make sure that the column is included in the select columns tile at the end of the ETL. Next, I would edit the card in Analyzer and click on the Date Range selector in the top right. Ensure that Hide Date on Card Details is not selected and that the appropriate Date Range Field is selected.

    image.png

    **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.
  • Newbie23
    Newbie23 Member
    Answer ✓

    Thanks Mark and Grand for your support, I was able to get the Data Range on my chart, it appears I didn't have all the columns selected at the end of the ETL.

    image.png

  • MarkSnodgrass
    MarkSnodgrass Coach

    @Newbie23 glad we could help. If you can mark any answers that helped you as accepted, that will help others in the community.

    **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