Change column data type

dacorson
dacorson Member
edited August 2021 in Magic ETL

I have a column with a date as a string, example 01/01/21. I want to convert to a date. I attempted using string to date and date format in Magic ETL 2.0, but I got an error message saying that it couldn't parse the text field to date. Then I tried the alter column tile, which also threw an error. I turned off Magic ETL 2.0 and tried the set column type tile, which worked. Is the alter column in 2.0 different from set column type? Does anyone know what I need to do in 2.0 to get this to work?

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @dacorson

    Magic ETL 2.0 doesn't know if your format is MM/DD/YY, DD/MM/YY, YY/MM/DD... etc by default. You need to explicitly define your format using a format string within the Alter Columns tile for your field. To do this you need to click the gear icon, then select the Date Settings option. In here you'll need to select "Custom Format" for Date & Time settings then define the format of your date string.

    %m = Month

    %d = Day

    %y = 2 digit year

    In this example I assumed Month/Day/Year:


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

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @dacorson

    Magic ETL 2.0 doesn't know if your format is MM/DD/YY, DD/MM/YY, YY/MM/DD... etc by default. You need to explicitly define your format using a format string within the Alter Columns tile for your field. To do this you need to click the gear icon, then select the Date Settings option. In here you'll need to select "Custom Format" for Date & Time settings then define the format of your date string.

    %m = Month

    %d = Day

    %y = 2 digit year

    In this example I assumed Month/Day/Year:


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