Converting text to date in ETL

Hi all,

 

I'm trying to convert a text into a date field in magic ETL, and I'm running into errors and unexpected outputs.  Screenshots below.

When trying to just convert the (numbers as text) into dates, I'm receiving the error "An invalid date was found".  When I conver that column first from text to numbers there's no issue, but then when I go to convert that new column to dates, I'm receiving all dates as 1/1/1970.

I've already accounted for non-numerical text fields in the filters previously.

Any help would be appreciated.

 

Structure of Data

Capture.JPG

Output of changing the above field to date

Capture2.JPG

 

Output of changing field to number first then to date

Capture3.JPG

 

Best Answer

  • MarkSnodgrass
    Answer ✓

    Ok. This should work for you. I've added some additional columns just for learning purposes so you can see how it is put together. The column that I named NewDate is really the only column that you would need in your output. I've added some comments to explain what each part is doing.

    SELECT "datelen",
    --Gets the year
    RIGHT("datelen",4) Yr,
    --Gets the day
    LEFT(RIGHT("datelen",6),2) Dy,
    --Gets the month
    --check to see if this is a 2-digit month or not
    CASE WHEN LENGTH("datelen") = 8 THEN
    LEFT("datelen",2)
    ELSE
    --add a leading zero for one-digit month
    LPAD(LEFT("datelen",1),2,'0')
    END Mo,
    --concatenate the parts together to a convertible format
    CONCAT(
    CASE WHEN LENGTH("datelen") = 8 THEN
    LEFT("datelen",2)
    ELSE
    LPAD(LEFT("datelen",1),2,'0')
    END,
    CONCAT(LEFT(RIGHT("datelen",6),2),RIGHT("datelen",4))) FullDate,
    --put it all together and convert it to a date format
    to_Date(
    CONCAT(
    CASE WHEN LENGTH("datelen") = 8 THEN
    LEFT("datelen",2)
    ELSE
    LPAD(LEFT("datelen",1),2,'0')
    END,
    CONCAT(LEFT(RIGHT("datelen",6),2),RIGHT("datelen",4))),'MMDDYYYY') NewDate

    FROM "df_ga_int_emarsys_campaign_attribution_sql_prep"

    Hopefully, this works for your data, but it depends if there is some bad data that isn't taken into account.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • I seem to recall running into a similar issue and I could never get the ETL to cooperate. I ending up doing a SQL dataflow to get the dates in an actual date format and then used that output dataset as my new input dataset in the ETL. Here is an example of what I did in my SQL dataflow 

    CASE WHEN "EXTRACTDTE" < 19000101 THEN NULL ELSE date(to_char(TRIM("EXTRACTDTE"),'99999999')) END AS DateEXTRACTDTE

    I had about 10 different date fields in my dataset and I applied this same logic to each field. 

    Hope this helps.

     

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Thanks - I'm trying that and running into an error:

    The database reported a syntax error: FUNCTION to_char does not exist
    See attached screenshot.

     

     

  • It looks like I was using the Redshift SQL for this conversion. Do you have the option to use that? If not, we should be able to track down the equivalent functions in MySQL.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Using redshift, this is my code:

    SELECT
    `Campaign`,
    `Date`,
    `Revenue`,
    CASE WHEN `DateLen` < 19000101 THEN NULL ELSE date(to_char(TRIM(`DateLen`), "99999999")) END AS DateEXTRACTDTE
    FROM
    `df_ga_int_emarsys_campaign_attribution_sql_prep`

    And I'm getting the error:  

    The database reported a syntax error: [Amazon](500310) Invalid operation: syntax error at or near "`" Position: 231;
    Thanks for your help, I'm not very familiar with using these types of dataflows.
  • Redshift and MySQL have different preferences on their use of single quotes and double quotes. 

    Try this:

    SELECT
    "campaign",
    "date",
    "revenue",
    CASE WHEN "datelen" < 19000101 THEN NULL ELSE date(to_char(TRIM("datelen"), '99999999')) END AS DateEXTRACTDTE
    FROM
    "df_ga_int_emarsys_campaign_attribution_sql_prep"
    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • The SQL worked but that field just came back blank...Capture.JPG

  • I would say that means it is falling into the first part of the case statement and it thinks the values are less than 19000101. Can you add another screenshot that shows the datelen column without any logic applied to it?

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • MarkSnodgrass
    Answer ✓

    Ok. This should work for you. I've added some additional columns just for learning purposes so you can see how it is put together. The column that I named NewDate is really the only column that you would need in your output. I've added some comments to explain what each part is doing.

    SELECT "datelen",
    --Gets the year
    RIGHT("datelen",4) Yr,
    --Gets the day
    LEFT(RIGHT("datelen",6),2) Dy,
    --Gets the month
    --check to see if this is a 2-digit month or not
    CASE WHEN LENGTH("datelen") = 8 THEN
    LEFT("datelen",2)
    ELSE
    --add a leading zero for one-digit month
    LPAD(LEFT("datelen",1),2,'0')
    END Mo,
    --concatenate the parts together to a convertible format
    CONCAT(
    CASE WHEN LENGTH("datelen") = 8 THEN
    LEFT("datelen",2)
    ELSE
    LPAD(LEFT("datelen",1),2,'0')
    END,
    CONCAT(LEFT(RIGHT("datelen",6),2),RIGHT("datelen",4))) FullDate,
    --put it all together and convert it to a date format
    to_Date(
    CONCAT(
    CASE WHEN LENGTH("datelen") = 8 THEN
    LEFT("datelen",2)
    ELSE
    LPAD(LEFT("datelen",1),2,'0')
    END,
    CONCAT(LEFT(RIGHT("datelen",6),2),RIGHT("datelen",4))),'MMDDYYYY') NewDate

    FROM "df_ga_int_emarsys_campaign_attribution_sql_prep"

    Hopefully, this works for your data, but it depends if there is some bad data that isn't taken into account.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Works perfectly, thank you for your help!