How to change a string to date ?

Hi all,

Domo has identified the Date Column as Text/String. I need to use this column in the quick filters on the card. I tried using STR_TO_DATE function, but it is not helping

The data in the Date Column is mm-y format - Jan-18 (Example)

Kindly help

Find more posts tagged with

Comments

BlueRooster

If you're missing a day, you'll have to add one before using the STR_TO_DATE function.

You could try this:

STR_TO_DATE(CONCAT('01-',`DateColumn`), '%d-%b-%y')

That should convert all month/year strings to a usable date.

Let me know if you have any other questions,

ValiantSpur

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

user06240

Thanks for the quick reply

The solution you suggested worked !

The dataset I have is aggregated at monthly level and I need to use this field in the quick filter with Months starting from Nov, 2017 till March, 2018

The quick filter should have values such as Nov, 17, Dec,17, till Mar,18

Kindly suggest if it is possible

BlueRooster

If you're using whole month aggregates, the only method would either be to default the months to always use the first day of the month as in my original suggestion (this would allow you to use a date quick filter).

The other option if you don't want days is to leave it as a string with Month/Year and the user would have to select each individual month.

There's not a way to have it select "between" two aggregated dates.

ST_Superman

You could set up a beastmode to filter the dates that you want. Although you would not be able to alter the dates via the quickfilter any more.

case when `Date Column` IN('Nov-17','Dec-17','Jan-18','Feb-18','Mar-18') then 'True' else 'False' end

Or, if you change your string column to a date first:

case when `Date field` >='01-Nov-17' and `Date field` <='01-Mar-18' then 'True' else 'False' end

You could then use that beastmode as a filter. If you select only the 'True' rows, then your data will be filtered for dates betwee Nov-17 and Mar-18

user06240

If I use the second option of keeping the date column as is (String), in the quick filter it is sorted based on the Alphabets

The result that comes out is in this order

Dec-17, Feb-18, Jan-18...

The dates should be arranged chronologically

ST_Superman

Correct, if you leave it as a string, then the quick filter is sorted alphabetically. If you change it to a date, you need to include the day as well.

Do these months refer to a specific time frame for your fiscal year? You could use a beastmode to label it something like '2017 Q4', etc. Just be thoughtful about what you decide to call it so that when it is sorted (alphabetically) it displays in the correct order.

It may be easier if you could provide a screenshot of what you are working with and how you would like it to be displayed