Date Conversion

TMonty0319 · 2024-02-20T21:05:06+00:00

There was an error rendering this rich post.

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

Find more posts tagged with

Date

Magic ETL

Accepted answers

MarkSnodgrass

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.

Jbrorby

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

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

All comments