Adding year value to MM/DD date field.

user01145
user01145
in Magic ETL

I have a free text note field that we preface each note with a MM/DD format date. I am trying to add the year to this field so that Domo can chronologize this field and compare this free text date with things like system driven dates. 

 

I am currently using the following beast mode to extract the date from our text field, which works however the year output is always '0001'.

 

STR_TO_DATE(REPLACE(LEFT(`TextLine1`,5),' (',''),'%m,%d')

 

How can I force Domo to add either the current year or manaually add a specific year to this date field? 

Tagged:

Best Answer

  • dthierjung
    dthierjung
    Answer ✓

    You should be able to use the CONCAT() function in combination with YEAR(NOW()) to get the current year, and append it your string.

     

    Here's an example that manually builds a date representing the current date, but 1 year ago:

     

    str_to_date(

        CONCAT(

           YEAR(CURDATE()) - 1 , '-', MONTH(CURDATE()) , '-', DAY(CURDATE())

        ),

    '%Y-%m-%d')

     

    Let me know if the above doesn't help or isn't clear.

Answers

  • dthierjung
    dthierjung
    Answer ✓

    You should be able to use the CONCAT() function in combination with YEAR(NOW()) to get the current year, and append it your string.

     

    Here's an example that manually builds a date representing the current date, but 1 year ago:

     

    str_to_date(

        CONCAT(

           YEAR(CURDATE()) - 1 , '-', MONTH(CURDATE()) , '-', DAY(CURDATE())

        ),

    '%Y-%m-%d')

     

    Let me know if the above doesn't help or isn't clear.

Categories