Adding year value to MM/DD date field.

user01145

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? 

dthierjung

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.

dthierjung

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.