Date Format change

louiswatson

I have a few columns which use the date format 01/Jan/2016, I need to convert these to 'Date' to use as date ranges in metrics.

I have tried using the 'replace' text card in an ETL to change the forward slashes and months to numbers. This works on some rows but not all (trying to figure out why). Then I change the data type to 'Date'. I don't have any errors but there is a lot of data missing.

Is there an easier way for me to convert these columns into date format?

Many thanks

Ashleigh

@louiswatson you can also try using the input dataset data handling section. Click on your input in an ETL and then select data Handling, you can choose data type Date and then click the settings icon for more details.

MichelleH

@louiswatson You can use the STR_TO_DATE() function to convert those values to date like this:

STR_TO_DATE(`DateField`,'%d/%b/%Y')

The second argument of the function uses these date format specifiers to specify what format the existing field uses.

Ashleigh

@louiswatson you can also try using the input dataset data handling section. Click on your input in an ETL and then select data Handling, you can choose data type Date and then click the settings icon for more details.

trafalger

Sometimes figuring out the date syntax can be annoying - I usually ask chatGPT like "I have text like "12/31/23" how do I make it a date in MySQL" and it will spit out the proper syntax… most of the time.