Adding year value to MM/DD date field.

user01145 · February 2019

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 · February 2019

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 · February 2019

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.

Magic ETL

Magic ETL

Adding year value to MM/DD date field.

Best Answer

Answers

Welcome!

Welcome!

Quick Links

Categories