Create beast mode dates

mroker

Hi everyone, help needed! Here is how my table looks below. What I want to show in Analyzer is the count of accounts of these services over time. My problem is how the dates are shown here. Any advice on what I can do? I thought about creating a calculated field to index dates but not sure how to do it. Any help is appreciated!😀

Account Service Start Stop

1 mobile jan 26 aug 14th

4 tv apr 9 sep 20th

6 radio jan 9th aug 4th

MarkSnodgrass

The STR_TO_DATE function is going to be your friend in this case. Try this for your beast modes.

Start

STR_TO_DATE(
  CONCAT(
    REPLACE(
      REPLACE(
        REPLACE(
          REPLACE(`Start`,'th','')
          ,'st','')
        ,'nd','')
      ,'rd','')
    ,' ',YEAR(CURRENT_DATE())
    ),'%b %d %Y')

Stop

STR_TO_DATE(
  CONCAT(
    REPLACE(
      REPLACE(
        REPLACE(
          REPLACE(`Stop`,'th','')
          ,'st','')
        ,'nd','')
      ,'rd','')
    ,' ',YEAR(CURRENT_DATE())
    ),'%b %d %Y')

Here's the breakdown of what I'm doing from the inside out.

Use the replace function multiple times to remove the th, rd, st, nd from the day of the month.

Add the current year by using the year function and the current date function so you have a complete date.

Use the concat function to build a string that would look something like this: Apr 4 2022

Use the str_to_date function to convert that to a standard date. The %b %d %Y informs the function what format the string is currently in.

Hope that makes sense.