Convert text to date field using

Newbie23 · 2023-05-09T16:34:25+00:00

There was an error rendering this rich post.

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.

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"

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

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

Find more posts tagged with

Accepted answers

MarkSnodgrass

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.

MarkSnodgrass

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.

Newbie23

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.

All comments

GrantSmith

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

Newbie23

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.

GrantSmith

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

Newbie23

still not working, getting the same error

MarkSnodgrass

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.

Newbie23

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.

MarkSnodgrass

Newbie23

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.

MarkSnodgrass

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