How to change a string to date ?

user06240
user06240 Member
in Archive

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

Comments

  • BlueRooster
    BlueRooster Domo Employee

    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
    user06240 Member

    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
    BlueRooster Domo Employee

    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
    ST_Superman Domo Employee

    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
    user06240 Member

    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
    ST_Superman Domo Employee

    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.  1.png

     

     

    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 

Categories