Date Conversion

TMonty0319
TMonty0319 Member
in Magic ETL

I need a formula for taking just the month name from this format - Thu, 29 Feb 2024 11:59 PM. I need it in a date formatted column though. I can take the Month Name using MONTHNAME('my date column') but i cant get the column to be date. Its always text. Any advice? Thanks

Tagged:

Best Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You can get this formatted as a date field by using the STR_TO_DATE() function like this:

    STR_TO_DATE(yourfield, '%a, %d %b %Y %h:%i %p')

    You can use the monthname function around this function to extract the month name, but that will be a string value since it is text.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Jbrorby
    Jbrorby Contributor
    Answer ✓

    You can do this, but its going to add '1, 0001' for days and year.

    STR_TO_DATE(MONTHNAME([your date column]), '%M')

    Can you explain why you need it to be a date column? Maybe there is a round about way to achieve what you're trying

  • DomoDork
    DomoDork Contributor
    Answer ✓

    Hi @TMonty0319,

    This should do the trick:

    DATE_FORMAT(STR_TO_DATE(TRIM(SUBSTRING(<your date field>,INSTR(<your date field>,',')+1)), '%d %b %Y %H:%i %p'),'%Y-%m-%d %H:%i:%s')

    This finds the first occurrence of the comma in your string and removes the day abbreviation since you dont really need that (since the day month and year follow it). We then trim it to remove any leading/trailing spaces and apply STR_TO_DATE to convert it to a datetime. However that leaves you with a unix style timestamp. So we then apply ISO date format to normalize it to a standard datetime using DATE_FORMAT. I hope this helps

    image.png

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You can get this formatted as a date field by using the STR_TO_DATE() function like this:

    STR_TO_DATE(yourfield, '%a, %d %b %Y %h:%i %p')

    You can use the monthname function around this function to extract the month name, but that will be a string value since it is text.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Jbrorby
    Jbrorby Contributor
    Answer ✓

    You can do this, but its going to add '1, 0001' for days and year.

    STR_TO_DATE(MONTHNAME([your date column]), '%M')

    Can you explain why you need it to be a date column? Maybe there is a round about way to achieve what you're trying

  • DomoDork
    DomoDork Contributor
    Answer ✓

    Hi @TMonty0319,

    This should do the trick:

    DATE_FORMAT(STR_TO_DATE(TRIM(SUBSTRING(<your date field>,INSTR(<your date field>,',')+1)), '%d %b %Y %H:%i %p'),'%Y-%m-%d %H:%i:%s')

    This finds the first occurrence of the comma in your string and removes the day abbreviation since you dont really need that (since the day month and year follow it). We then trim it to remove any leading/trailing spaces and apply STR_TO_DATE to convert it to a datetime. However that leaves you with a unix style timestamp. So we then apply ISO date format to normalize it to a standard datetime using DATE_FORMAT. I hope this helps

    image.png
  • TMonty0319
    TMonty0319 Member

    I just need the month name. So from this - Thu, 29 Feb 2024 11:59 PM i need a formula for a new column that just has February and is in Date Format.

Categories