Convert String To Date Format

micheleb

Hello,

I'm working in Magic ETL.

I have two problems

1 I have a Hire Date field with a string data type that has the string in 2 different formats

01/01/19 or 01/01/2019 How can I convert the values to one format?

2 How can I convert the standardized string to a date. I tried to use str to date but it failed

each time.

GrantSmith

Hi @micheleb

You'll need to put everything in the same format and then convert it to a date. You can convert the date strings using a REGEX_REPLACE function in a formula tile:

REGEXP_REPLACE(`Date`, '^(\d{2})\/(\d{2})\/(\d{2})$', '$1/$2/20$3')

This will check for a 2 digits / 2 digits / 2 digits format and then put a 20 in the last field to default to this millennium so that you have a 4 digit year. If it doesn't match the format it will still just return the string so your MM/DD/YYYY format strings will be untouched.

Once they're in the same format you can then use an Alter Columns tile to change the type of the date column from string to Date.

GrantSmith

Hi @micheleb

You'll need to put everything in the same format and then convert it to a date. You can convert the date strings using a REGEX_REPLACE function in a formula tile:

REGEXP_REPLACE(`Date`, '^(\d{2})\/(\d{2})\/(\d{2})$', '$1/$2/20$3')

This will check for a 2 digits / 2 digits / 2 digits format and then put a 20 in the last field to default to this millennium so that you have a 4 digit year. If it doesn't match the format it will still just return the string so your MM/DD/YYYY format strings will be untouched.

Once they're in the same format you can then use an Alter Columns tile to change the type of the date column from string to Date.

MarkSnodgrass

Do you have non-date data in this field? If not, when you are in the ETL you can select that input dataset and on the configuration tab, change the data type from text to Date (assuming that it is currently text). Domo should auto-format your dates to normalize this data for you. If this doesn't work for you, let me know and there are some beast mode things you can do, but this is the easiest solution.

micheleb

Thanks so much ! The Regex formula worked perfectly. I still had some folks who were hired in the 1900's but I'm thinking I could use a an If then statement like If Hire Date year is 2050 or greater to switch it to 1900's

MarkSnodgrass

@micheleb just out of curiosity, did you try a beast mode like this:

DATE(yourdatefield)

I don't have a sample dataset to try it on, but wondered if that throws an error, or if it standardizes the data for you.

micheleb

The problem is the data in the column is not uniform so trying to use beast mode or changing data types won't work without changing the strings to consistent standard format won't work for a beast mode or simple data type change. Some strings are formatted 00/00/00 some strings are formatted 00/00/0000 some are null.