Date Format change

louiswatson
louiswatson Member
in Magic ETL

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

Best Answer

  • Ashleigh
    Ashleigh Coach
    Answer ✓

    @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.

    image.png
    image.png

    **If this answer solved your problem be sure to like it and accept it as a solution!

Answers

  • MichelleH
    MichelleH Coach

    @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
    Ashleigh Coach
    Answer ✓

    @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.

    image.png
    image.png

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • trafalger
    trafalger Coach
    edited September 2023

    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.

Categories